Compressing MSSQL

any way to get back lost space?

[mattglet]

i have a sql database that is growing increasingly big. i'm wondering if there is a way to "optimize" it (i.e. MySQL), or if that's even a viable idea? i have seen the auto-shrink option, but i haven't seen what the actual purpose of it is.

are there any other tools i could use to maximize the space used by the database?

-Matt

[IanTurner]

Are you referring to MySQL or MS SQL Server?

Also I suspect you may mean minimise the space used by the database :)

[mattglet]

i am referring to something to use with MSSQL, that compares to the OPTIMIZE MySQL function.

and yes, i meant minimize.

-Matt

[IanTurner]

Have you looked at the database maintenance plans, under the SQL Server 'Management' section, you can do some basic compression work there.

[Compworld]

Just compress the log file and the used space. You would be surprised on how much space can be released once this is done. This would allow you to maximize your SQL disk space w/o deleting any of the files. I run the query at least once per week. But our site is very active, so your needs may be different. Its a basic command in SQL Enterprise Manager.

CompWorld

[mattglet]

is this the SHRINKFILE command?

-Matt

[plumsauce]

is it your transaction logs that are getting bigger?

[mattglet]

well, i can't really tell to be honest with you... i checked the database size via the properties menu, and that's how i'm determining the amount of data. not sure if that figure includes the t logs.

-Matt

[Dreamquick]

Shrinking physical databases doesn't tend to recover a lot of space unless you re-organise the data too and that's sometimes quite an intensive task on large DB's since it involves lots of disk thrashing while data is moved.

("Shrink database" option is on the database level context menu, it lives under "all tasks", or you can just use the option on the taskpad view)

Shrinking logfiles is really easy but obviously stops you rolling back changes that are listed in those transaction logs. It's easy to let these self-manage if you don't really need them - just select "auto truncate logfiles" and "auto shrink" on the database options tab - this only keeps as much in them as is actually needed at the moment.

If you want to force the logs to shrink immediately it's not that hard to find scripts to do it (drop me a sticky if you'd like a link to a shrinking script)

- Tony

[plumsauce]

what i'm getting at, is that until you do a
backup, the transaction logs keep growing.

auto-truncate on checkpoint will work,
but consider the recovery issues.