The damage you do when shrinking SQL Server database files

JJ Prinsloo SQL Expert Database Shrinking Unknown Road Ahead

I have heard of, seen, investigated, and debated about so many problems relating to database sizes, disk space, and shrinking SQL Server database files. It always puts a question mark on my face when listening to so many people offering bad advice and/or solutions to these problems.

“Just shrink the file”. Seems to be the general consensus here. DON’T!! I understand the urge. You might be a new DBA and want to show you can solve the problem as soon as possible. The problem is that you may be creating additional problems in the background that you are not aware of.

The problem with shrinking your files is that you are only solving the immediate problem on the surface whilst creating additional problems deeper down. Shrinking your files causes SQL server to move data around within the physical files. This in turn causes extreme index fragmentation. Alright, so we can just rebuild the indexes right? Well in theory yes, and this will sort out the fragmentation issue. The problem now is that your database size has grown again and you are back to square one. This turns into a vicious cycle.

So then what should I do??

As with everything SQL this will depend on your requirement and SLA. A good practice in any event will be to consider the following options:

  1. Determine where the size issue is on your database. Is it within your data files or the transaction log?
  2. If the space issue is within your data files, the first thing to consider is leaving it. The purpose of your drive is to store files. There is no point in carrying around an empty backpack. Whether the free space is on the drive itself or within the data file it does not matter. You have ample free space available for the database to do what it needs to.
  3. If the transaction log is growing uncontrollably and not shrinking, it is most likely that you are using a full recovery model on your database. If this is the case and your transaction log is not shrinking, then you are not taking proper log backups of your transaction log. Review your backup policy and SLA agreements of the business which should point out one of the following options:
    1. You might find that it is not necessary to perform in point restores on a database. If this is the case, consider switching the database recovery model to simple instead of full. Just keep in mind that you will not be able to recover to a specific point in time but only to the time of the last full backup.
    2. If it is necessary to restore to a specific point in time you will have to design a backup solution that fits these needs. Part of your solution should include taking backups of the transaction log which will truncate the log automatically and free up unused space. Unfortunately, full backups won’t do this because of the backup chain.
  4. Review your auto growth settings. Are you using the default 10%? If so, consider changing this to a decent amount of bytes rather than percentage. 10% of a 1TB file = 100GB. Wowza!
  5. Get more disk space. This is not something to be considered lightly as by working through the previous few steps you have more than likely came across one or two things that need to be optimized or changed. More disk space should be the last item on this list as there will be a direct cost to the business which might have been avoided. In any event though, this is sometimes necessary.

How do I prevent this in the first place?

A good practice is to plan for your data usage requirements as it will be in the future and not for what your requirement is right now. This will save you a lot of headache. If you size your databases to cater for the amount of data you will have in a years’ time, you’ll be grand.

Work on a proper backup solution from day one. Review the SLA and business requirements and set your database recover models to match the SLA’s. Design your backup solutions around this and if you go with full recovery, take regular transaction log backups which in turn will keep your transaction log file sizes at bay.

Once again, make sure your auto growth setting is set to a reasonable amount of fixed bytes rather than a percentage. Percentage settings on these can quickly become a handful to deal with.

Great, I still need to shrink my files for other reasons.

I admit that sometimes you cannot avoid the shrinking of a data file. You may have deleted a huge chunk of data after archiving it and the database is not expected to grow which makes it necessary to shrink the files. After all, that space can be used for the data files of other databases.

If this is the requirement, consider following the following alternative approach:

  1. Backup your database (ALWAYS BACKUP your database)
  2. Create a new filegroup
  3. Move all the affected tables and indexes to the newly created filegroup. This process will also remove any fragmentation automatically
  4. Delete the previous file group where you moved the tables and indexes from. In the case where this is the PRIMARY filegroup, shrink it way down.

To recap

Make sure your settings on your database is correct, that your backup policies and procedures are in place, and that these agree with your SLA.

Make sure you plan ahead regarding the required storage space for your data.

NEVER shrink your data files, especially not as part of your automatic maintenance procedures (It sounds harsh and extreme but as a DBA, this is how you should think about it)

If it’s not a problem, leave it! As mentioned before, your disk is there to be used and there is no difference between free space on the disk and free space in your data file.

Take the time to understand the road ahead and plan for what is coming. It could become a slippery slope.

2 replies
  1. Stacy Gray
    Stacy Gray says:

    Great explanation of why one shouldn’t shrink the database itself (vicious cycle). You make it sound like the only reason why the tranlog might grow excessively is because it isn’t being backed up frequently enough and there are other reasons as well: AOAG loss of connectivity to secondary, replication, open transaction, long running transaction, etc. It might be good to explain that active transactions do not get truncated during tranlog backup and that it is also possible for a transaction log to run out of disk space while in simple recovery model. Just a thought.

    Reply
    • Joachim Prinsloo
      Joachim Prinsloo says:

      Hi Stacy,

      Thank you for your valued input. The article was particularly written on database size and shrinking (without going into too much detail around log files and the reason they grow). I have found that around 80% of the time with newer DBA’s the problem to an out of control log file is usually because of ineffective backup plans.

      That being said, I read your comment and afterwards reread the article. You make very interesting points and it always helps to get the perspective of others reading my work. Re-reading the article with your comments in mind pointed out that the article somehow generalizes. Generalization is something that I am not a huge fan of. Would you mind if I added your comments (with attribution of course) to the article above to make it a more robust piece?

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *