Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance .

So i would like to know from you guys have you ever used the shrink database command if yes then in which parctical scenarios we should be using them

share|improve this question

migrated from stackoverflow.com Jun 5 '12 at 12:53

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

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:

  • 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

share|improve this answer
add comment

Almost never.

Valid use cases? Some examples:

  • To fix a bloated log file
    Say where recovery model was FULL and no log backups were done.
  • Development machines to save space: these won't have production loads
  • Read only databases

But your investigation is correct: any kind of shrink should not be a routine, scheduled operation

When you do need a shrink, you'd use SHRINKFILE instead which is more targeted

share|improve this answer
    
And, regarding the bloated LDF file, shrinkdatabase usually doesn't even work alone because of how virtual logs are allocated and rotated. (I found out I had to simulate some "load" while repeatedly shrinking.) technet.microsoft.com/en-us/magazine/2009.02.logging.aspx –  Jirka Hanika Jun 5 '12 at 7:30
    
@JirkaHanika: true, but perhaps too deep for this question.. –  gbn Jun 5 '12 at 7:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.