Tell me more ×
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.
up vote 2 down vote favorite

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, etc.

Now the doubt I have is what are the scenarios where I should use the shrink option in the database.

I have never seen a scenario it was stated as the useful one. Is shrinking always evil?

share|improve this question

4 Answers

up vote 7 down vote accepted

You'll know when you need to shrink your database files, because it'll most likely come from a requirement to reclaim disk storage. Even if there is a database that has a large amount of free space in it, I usually still like to keep it as such without shrinking. No matter what people try to claim that it'll never use all that space, unless the database is read only then there is no guarantee for that. Obviously, that's within reason. If you for some reason have 1 TB free space on a 10 GB database, then that may warrant a shrink, unless it's expected to regrow at a quick rate.

The most important aspect about database file shrinking is to only do it when it is a hard requirement and do not do it routinely.

share|improve this answer
can you lil bit expand some situations of hard requirement?? I am not greedy about the space. But i would love to get when exactly to be used? – Senthil Prabhu May 23 at 12:48
3  
The hard requirement is when the business/application team makes a demand that is non negotiable. Another case would be where your log file may completely fill the drive (due to no log reuse) and you may want to reclaim for a little free space buffer. These situations will be rare, but they can/will happen. These (and a few others) are the exceptions to the rule. – Thomas Stringer May 23 at 12:50
I completely understand when it is demand from the application team. How can we say a log is no longer usable? – Senthil Prabhu May 23 at 12:57
It's not that the log is no longer usable (as it'll always be so). Say your transaction log is on a 10 GB volume. During normal operations and routine log backups, your log file is usually 500 MB. But say a transaction log backup job fails consistently, or there is an open transaction. These situations will prevent SQL Server from reusing the transaction log, and depending on your autogrowth settings your log file could fill up the drive (also depends on the maxsize setting). So say your log file grows to 10 GB filling up the drive. You alleviate the problem, but your physical NTFS 1/2 – Thomas Stringer May 23 at 13:00
file continues to be 10 GB. In that case, you may want to shrink that file in order to reclaim space to have free space left on that drive. Again, this is just a simple example. – Thomas Stringer May 23 at 13:01
show 2 more comments
up vote 4 down vote

There are some cases to be made for shrinking a database. The main one is where you've had a database for a long time, and just deleted a load of data, and know that you're not going to need the database to be that size again. Ever.

Every time you grow a database file, you run the risk that the file will be fragmented on the disk, leading to poorer performance. Not to mention the slight hiccup in performance as the file is expanded, either automatically (which is likely to be when someone is working), or manually.

Seriously. If you don't have an overwhelming need, then don't do it.

share|improve this answer
As I can't Select two answer I have Up-vote for you. – Senthil Prabhu May 23 at 12:51
up vote 3 down vote

A good scenario on where you would need to shrink a database file is to remove Virtual Log Files in your transaction log. VLF's can come about because of improper growth and sizing strategy, or just kind of creep up on you over time. One of the maintenance tasks we have is to monitor VLF's. If you find your database has a large number of VLF's then shrinking is a very handy way to deal with it. There is more to it than that, but that is a good situation where you would use shrink to deal with a problem. Here are some links:

Check VLF's

More VLF stuff, with other good links

share|improve this answer
up vote 0 down vote

In my current project, shrinking is a weekly part of our lives. Not into production, of course, but our various testing environments. When your live DB can take a huge amount of space, much of which isn't needed on development and testing servers, it can be a feasible idea to parse the unnecessary information from the production DB. Then shrink it to some 1/3:rd or 1/2 of its original size, and distribute that copy to various other environments where not all the production data is absolutely necessary.

Especially in larger projects where the space usage can be increased 10-fold when it's reflected in all the other environments, shrinking the non-production DB's can save up a huge amount of space with very little trouble.

share|improve this answer
If you do shrinking won't it affect the developers concern. How do they manipulate the performance of the system.? – Senthil Prabhu May 24 at 10:13
Considering the thousandfold increased stress on performance issues in production, the effects of the fragmentation is barely noticeable on dev / testing side. Most of the tests are focused on the actual database model & data, or some individual optimizations where rebuilding / reorganizing the required indexes is practically a non-issue. Besides, not all dev environments should be using production data anyway, just some of those stages. I realise however that this may vary depending on project, but it's worked for us thus far. – Kahn May 24 at 13:24
Also, as I understand it, repeated shrinking is the cause behind most problems as long as you remember to maintain the indexes, since that would cause fragmentation on HD level. When we only ever shrink one copy once, and distribute that, there's no problem from repeated shrinks. – Kahn May 24 at 13:27

Your Answer

 
discard

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

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