patternsqlMinor
Why do we have SHRINKDATABASE command in SQL Server
Viewed 0 times
whysqlservercommandhaveshrinkdatabase
Problem
I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance .
I would like to know from you guys have you ever used the shrink database command if yes then in which practical scenarios we should be using them.
I would like to know from you guys have you ever used the shrink database command if yes then in which practical scenarios we should be using them.
Solution
First and foremost lets distinguish between the need for SHRINK and it's well known unintended side effect: possible increase of fragmentation. The later is an implementation side effect. In fact is perfectly possible to design a shrink that would reduce fragmentation (ie. has the same effect as a REORGANIZE) at the cost of significantly longer runtime.
The need for shrink is a perfectly valid one: reduce the size of a database that has grown for whatever reason. Whether is reduce the size after a massive one-time data delete, reverse the effect of a one time operation that caused unexpected size growth (eg. a bad import), migration to a much improved (and much smaller) data model, clean the fallout of some previous bad DBA management, they're all perfectly valid reason for shrinking a database. The cost of shrink (increased fragmentation) must be considered, of course. But consider a case when a badly design DB of 200Gb is reduced to just 20gb of data. Shrinking it to 50Gb size and rebuilding the indexes makes perfect sense. In other words the capability to shrink a database is needed.
What is frowned upon is the abuse of shrink:
As for the LOG shrink, the problem is even more direct: LOG can grow even on a well maintained system. Clear examples: transactional replication or DB mirroring deployments that run into problems with the distribution DB or the mirror partner. Until the problem is solved, the log must be retained by the publisher (for TX) or primary (for DBM). When the issue is finally resolved and the pinned log is cleared, you will end up with an unnecessary large log. This can and should be shrinked back to operational size. Shrinking the log is somehow seen as VooDoo ('I tried it and did not do anythin!', 'Hmm, I tried it and it worked now') but is easy once you understand that in order to shrink the log the tail of the log must be free which implies that, counter intuitively, more log has to be generated (and freed) before the file can be shrunk. Is all explained in How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
The need for shrink is a perfectly valid one: reduce the size of a database that has grown for whatever reason. Whether is reduce the size after a massive one-time data delete, reverse the effect of a one time operation that caused unexpected size growth (eg. a bad import), migration to a much improved (and much smaller) data model, clean the fallout of some previous bad DBA management, they're all perfectly valid reason for shrinking a database. The cost of shrink (increased fragmentation) must be considered, of course. But consider a case when a badly design DB of 200Gb is reduced to just 20gb of data. Shrinking it to 50Gb size and rebuilding the indexes makes perfect sense. In other words the capability to shrink a database is needed.
What is frowned upon is the abuse of shrink:
- regular scheduled shrink
- manual shrink for unfounded reason ('I need to free some disk space for ...'), when often the database actually needs that size and will grow back overnight
- shrink for small yields (eg. reduce 200Gb by 5Gb)
As for the LOG shrink, the problem is even more direct: LOG can grow even on a well maintained system. Clear examples: transactional replication or DB mirroring deployments that run into problems with the distribution DB or the mirror partner. Until the problem is solved, the log must be retained by the publisher (for TX) or primary (for DBM). When the issue is finally resolved and the pinned log is cleared, you will end up with an unnecessary large log. This can and should be shrinked back to operational size. Shrinking the log is somehow seen as VooDoo ('I tried it and did not do anythin!', 'Hmm, I tried it and it worked now') but is easy once you understand that in order to shrink the log the tail of the log must be free which implies that, counter intuitively, more log has to be generated (and freed) before the file can be shrunk. Is all explained in How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
Context
StackExchange Database Administrators Q#18834, answer score: 8
Revisions (0)
No revisions yet.