Notes on SQL

Or rather, on a Test or Development server instance. If you need to do this to a Live server, then you have larger issues.

Most sites have servers for Developers and DBAs to work their dark magic, as well as various test servers for the next level of testing. These servers do, of course, have databases that nobody admits to creating and other more recognisable databases that have had huge amounts of test data inserted, updated, removed and then generally left, like a bloated whale carcass – taking far more space then they now need.

There are several options in these cases:

1. Remove the databases that are no longer required.
This isn’t a quick fix, when a server has run out of space and all development has come to an abrupt halt. The database might have a use that only a few people are aware of, and they’re off on holiday, in meetings or just ignoring my stroppy emails. The best solution in such cases is to take the database offline and wait a few days, so not an immediate fix for space shortage.

2. Move database files.
Also not necessarily an option. Some setups are in specific locations for a reason and there might be no viable storage anywhere else for that server.

3. Shrink the databases.
The most common option for these servers, but some can have a large number of files, so using SSMS for this can be rather tedious.

4. Ask for more Storage.

Yeah, right.

These servers aren’t treated well, but that is their purpose. It saves the QA and Live servers from a lot of grief later in the development cycle.

So, I had a look around for something that I could run against a server instance and would compress all of the databases for me. There are a few bits and pieces around, but nothing that quite did what I was after.
What I wanted was something that would give me some choice over what I compressed, because sometimes it is simply a waste of time to compress a very large file for for a little gain.

So, I have written something, stored it as a plain text file and it can be downloaded from here.

It doesn’t backup databases beforehand, nor does it check the health of the database (before or after). I was after something that performed the basics and saved me a lot of time.

And if you think this routine has solved all of your problems on your mission-critical systems, you really need to read this Paul Randal – Why You Should Not Shrink Your Data Files. The inside of your database will look like the car under those tank-tracks.

Tags: Compress, DBCC, FILEPROPERTY, NO_INFOMSGS, Shrink, SHRINKFILE, SPACEUSED, SQL