Share via

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!

Comments

  • Anonymous
    November 18, 2013
    "When do you actually need to run the shrink operation on data files?" One more case would be after you have enabled Data Compression and wanted to check how much of space was saved.

  • Anonymous
    November 18, 2013
    Not exactly. When you use compression, the free space inside the files should increase. I still don't think it is a good idea to reclaim this disk space by shrinking. The only thing I would shrink are transaction log files - I avoid data file shrinking as much as possible.