0

My understanding is that we should never shrink the database in production to free up space? Rather, we should shrink the Transaction log.

Could someone explain why? Thanks!

  • No, you shouldn't shrink either one, unless a one-time large growth has occurred that you don't need the extra space for under normal operations. – Erik Darling Apr 5 '17 at 16:59
  • what if we don't have any disk space to enlarge the database? – sw4949 Apr 5 '17 at 17:13
  • Well, never ever shrink the database to free up disk space to enlarge the same database. If the database has free space to shrink, then it will use its existing free space if it needs to store more data. – RDFozz Apr 5 '17 at 19:12
4

To elaborate on sp_BlitzErik's comment:

Generally, you should not be shrinking data or log files on a regular basis.

Shrinking the data file:

  • Impacts performance in the long run, as it can cause fragmentation (as it may have to move pages to create a contiguous block of free space at the end of the file);
  • Impacts performance while the shrink is happening;
  • is logged in the transaction log (and thus, can cause that to get bigger); and
  • will impact performance later, when more space is required and the file has to grow again.
  • also: risks failure if other files have used up the available disk space, and the file doesn't have room to grow again.

Shrinking the transaction log can be even more tempting, as it may normally show most of its space as unused. Most of the points above for the data file also apply to the log file (log file shrink won't be logged and cause the log file to grow), but it has some unique concerns:

  • The log file may require 30GB to support a job that runs every night at 2AM, but only require 500MB during the business day. So, shrinking it may be undone every day, making it pointless to do so.
  • The log file can suffer fragmentation (on SQL Server at least - search on "virtual log files").

Basically, the only time when shrinking either file makes sense is after unusual activity.

For the data file, if a large amount of data has been removed, and will not be repopulated over time, then it can make sense to shrink the file. If you have a database where data is remove and archived elsewhere once a year - it's being repopulated, and I wouldn't shrink the file. If you discovered a problem with that database, where one very large table was left out of the archive, and you cleaned it up and deleted 8 years of data - then, it might make sense to shrink the database.

Sometimes, you may decide to shrink a database after that annual archive, because there's hardly any free disk space on the server. This is a stopgap measure; I would make sure I had put getting more disk space in motion before I shrank the database, because you know you'll need it.

For log files, the circumstances when it makes sense to shrink them are even more limited. If your database is configured for point-in-time recovery (in SQL Server, the FULL recovery model), but you have not been taking transaction log backups, the transaction log could grow very large. Once you've got the transaction log process up and working, it probably makes sense to shrink the log file. Similarly, if you execute a single transaction that moves a lot of data, that could cause the log file to grow (it has to hold all the data to roll back the current transaction, regardless of the recovery model). If this is a process that occurs rarely (for instance, a twice-a-year load of millions of prospects into a sales database), it may make sense to shrink the log file afterwards. However, even here, allowing other files to grow into the space the transaction log required for that load may mean the load fails the next time you run it, because there's no longer enough free disk to grow the transaction log to the size it needs to be.

To summarize, shrinking data or log files does nothing to help your database run better, can make it run worse, and increases the likelihood that you'll run out of disk space down the road. If you're doing it to free space on your disk for other things, outside of the few specific cases where there's been known abnormal growth, you're only putting off the inevitable, and you better be working on getting more disk space somehow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.