1

I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real examples of issues that can pop up as a result of doing so?

Looks like the Tags are even against me on this one:

Shrink Tag - Irony

Improve this question
2
  • Have you already read the following article: brentozar.com/archive/2017/12/…
    – SQL_Hacker
    Commented Dec 12, 2019 at 15:53
  • It seems that only talks about the DBCC command ShrinkDatabase but I was more so curious about ShrinkFile. Either way, this is useful material I'll read through, so thanks!
    – J.D.
    Commented Dec 12, 2019 at 15:54

2 Answers 2

Reset to default
1

As far as the SQL Server product is concerned, the SHRINKFILE operation is safe. It is designed to respect locks, recovery models, transaction log usage, etc. In the absence of a bug in the SQL Server code, shrinking a database file won't cause corruption of data loss.

All code has bugs, so there is a chance that a routine operation like shrinking a data or log file could cause corruption. But this is true of anything. There was a bug in SQL Server 2012 and 2014 that caused corruption when rebuilding certain indexes. This mean doesn't that index rebuilds are unsafe.

All of the issues you'd run into when shrinking a data or log file are related to performance and maintainability (causing index fragmentation, having to wait on autogrowth events as the data size increases the file size again, etc).

Improve this answer
1

If you're making regular backups of the log file or are running in SIMPLE recovery mode, the log file should mostly consist of free space; shrinking it will just truncate the free space from the log file. This is usually safe to do, but you need to realize that the log file will likely just grow back to the previous size....when this happens the log will auto-grow, which can cause a performance hit every time it happens.

The only real reason for shrinking it is if you're low on disk space, if the log is filling up the drive you should consider adding additional disk space as a long term solution.

Improve this answer
3
  • Gotcha, so there's no chance of corruption or data loss when running in SIMPLE recovery model?...what about between backups of the log file (e.g. if I backup my log file every 5 minutes, in a highly transactional database I imagine a lot of data could be in the log still between backups)?
    – J.D.
    Commented Dec 12, 2019 at 18:15
  • 1
    I wouldn't go as far as to say that there's no chance of corruption or data loss, just that it's unlikely for that to be caused by shrinking the log file. In SIMPLE mode, you're going to have to resort to the latest full backup. In FULL recovery mode, you'll have to resort to the latest transaction log backup. I've seen situations like you're describing where a high transaction database has frequent log backups, and you're basically running continuous log backups, which hurts performance. It all comes down to what level of data loss you're going to be comfortable with should it happen.
    – MelvinLusk
    Commented Dec 12, 2019 at 18:49
  • Cool, makes sense. Thanks for the clarification.
    – J.D.
    Commented Dec 12, 2019 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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