Database maintenance and when is it needed (Part 2: SHRINK operations)

  • Article

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 :)

https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

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!