Database maintenance and when is it needed (Part 2: SHRINK operations)
This week I follow up the database maintenance discussion with the next step mentioned at the Database Maintenance Wizard. The Database Shrink step!
Let’s get one thing straight here. Shrinking a database should never be a part of any periodic database maintenance plan. Shrinking a database can cause severe index fragmentation and even file-level fragmentation. Repeating this process over and over for your database can be fatal to database performance. Check Paul Randal’s great blog post if you don’t believe me :)
Why a SHRINK database step was added to the “Database Maintenance” wizard, I have no clues. It can actually become an “anti-maintenance” for your databases. Let's see how you can avoid the shrinking of files, also let's see when you actually need to do a database shrink and finally if it is possible to make it go faster (VROOM!).
How can you avoid the shrinking of your database files?
- Pre-allocate disk space for your files and do not rely on autogrow.
- If you use autogrow, use decent values.
- Never use the default values of 1MB or 10%. Both autogrow values are performance killers.
- Do not shrink files just to save a bit of disk space, if the file will be full again in a few weeks.
- When you urgently need to reclaim disk space, prefer to shrink a transaction log instead of a data file.
When do you actually need to run the shrink operation on data files?
- Never, if possible
- When a really BIG data file has tons of free space inside it which will NEVER be used again. e.g. a 100 GB data file with 90 GB of free space that will never grow beyond 5 GB !
Can I make a shrink go faster?
- It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.
- Avoid having locks. No application should be connected to the database. Otherwise you might have locks and because of this the shrinking process will take longer to complete.
- Use trace flag 2548 to avoid LOB compaction. i.e. run this command before you execute the SHRINK operation: dbcc traceon(2548,-1)
I hope the above information will deter you from shrinking your data files :)
And next week, we will deal with the Reorganize and Rebuild Index steps!