Management Studio provides a neat GUI interface to define maintenance plans for your SQL databases. This tool is available under Management -> Maintenance Plans and provides many great Maintenance Plan Tasks that you can configure easily. There are many useful tasks such as “Check Database Integrity Task”, “Back Up Database Task”, “Rebuild Index Task”, etc.
One of the task is “Shrink Database Task” and should never be used as part of a scheduled maintenance plan. Essentially because it does nothing useful and does many awfull things.
Let’s start by listing the cons of shrinking a database:
1- First it takes time and a lot of IO and CPU. If you are lucky enough to have a time window when you can perform heavy maintenance tasks, you should use this time to do useful tasks such as rebuilding some indexes, updating you statistics, verifying the integrity of your databases, backing up your databases, testing your backups, loading and warming your cubes, etc.
2- Second a SHRINKDATABASE without the TRUNCATEONLY argument (which is essentially calling SHRINKFILE for each database data and log files) will attempt to move data to the beginning of the file. Doing so, it will induce index fragmentation: the data won’t be contiguous anymore and index scans will perform slower compared to non-fragmented index scans.
3- Also, by shrinking your files you are releasing space back to Windows (unless you specify NOTRUNCATE – and I can not imagine a case where specifying this argument would make sense). But is this a good thing? I have yet to see a database that shrinks over time so you will eventually have to grow the data file back. File grow takes time and recurrent file growth induces physical fragmentation which increases disk io latency.
The advantage of shrinking a database:
1- Shrinking the database release disk space. As a pointed out in the last bullet, this is not necessary a good thing.
You should consider shrinking only when the following conditions are met:
- You desperately need the space and can’t add disk space to the system
- You deleted a lot of data (or grew the data file too much) and you don’t expect the data files to grow back. A good example would be to shrink files which are on a filegroup that will become read-only, such as with date-based partitions.
- This is a manual operation and the shrink is not part of a recurrent maintenance task
- You first tried SHRINKFILE with the TRUNCATEONLY argument. The operation won’t induce fragmentation and the SHRINKFILE will be very fast.
RSS