2

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

At the current rate of growth, we are not going to use up 1.2Tb in more than 14 years. More to the point, we keep migrating functionality to PostgreSql whenever we need to make serious changes. In a very dynamic agile environment, this means we frequently migrate data away out of this SQL Server database. So, this database is likely to grow even slower in the future: it is being phased out, eventually.

As such, we'd rather use this unused empty space for other databases and such - moist likely it is not going to be needed for growing tables/indexes ever.

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case? Currently the server is running 2008 R2 EE, but we plan to upgrade to 2012 very soon. I think because this shrink functionality exists, there should be valid use cases where it makes sense to shrink. Otherwise why does it exist at all?

We can afford a few hours of downtime during a weekend.

Edit: the problem we are solving is as I said above: "we'd rather use this unused empty space for other databases". Also we'd rather prefer a solution that does not require investing too much time in learning the technology we are migrating out of.

  • What's the problem you're trying to solve? – Brent Ozar Jul 24 '14 at 23:00
  • Did you tried the suggestion I gave ? – Shanky Jul 25 '14 at 12:49
3

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case?

Yes. Dont touch that shrink button - Its a general rule and a very very sound advice from many renowned SQL Server Gurus - Paul Randal, Brent Ozar, Mike Walsh, Gail Shaw and many more.

The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

You have to be 1000% sure about your assumption that its growing but wont grow to this size in your proposed 3-5 years (its a 2 year difference as well :-)).

still somewhat growing -->You really need to find out at what pace it is growing. Are you doing database growth tracking by collecting the data growth over your complete business cycle ?

Now a days, data is what keeps the companies in business and databases are meant to grow.

Think about it - what are you going to do with the disk space that you gain from shrinking the database ?

Concentrate on more important areas of optimization as @AaronBertrand suggests.

Even if you decide to shrink , I would suggest you to

  • Do a reorg/rebuild and update stats after the nasty shrink operation
  • Take a full backup to be safe with all the indexes intact.
  • Adjust your auto-growth settings to not allow the auto-growth events kick off more frequently.

Edit

After OP's comments ---

Then go for it ... since you are or will be moving away data from SQL Server to PostgreSql. But do the post steps once as I mentioned in my answer - once you do the shrink operation. Also, use DBCC SHRINKFILE as you have more control of what you are shrinking as opposed to DBCC SHRINKDATABASE.

  • Regarding "You really need to find out at what pace it is growing" - I did. "Think about it - what are you going to do with the disk space that you gain from shrinking the database " - I already thought about it. I modified my question accordingly. – A-K Jul 24 '14 at 21:34
  • Thank you for the links, but I am not sure they are completely relevant: they mostly assume that the data is going to grow back, such as "Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again". This is not the case, they will not have to grow again. – A-K Jul 24 '14 at 21:45
  • @AlexKuznetsov Then go for it ... since you are or will be moving away data from SQL Server to PostgreSql. But do the post steps once as I mentioned in my answer - once you do the shrink operation. Also, use DBCC SHRINKFILE as you have more control of what you are shrinking as opposed to DBCC SHRINKDATABASE. – Kin Shah Jul 24 '14 at 21:46
2

The rule is not to never shrink. The rule is to not shrink when you will need to grow again.

Go ahead and shrink to reclaim the space. Deal with the resulting fragmentation. Be aware that shrinking can cause extreme disk load. These are pretty much the only problems resulting from shrinking.

Or, rebuild all indexes into a fresh filegroup and drop/truncate the old one. That can be by far faster and will defragment/compact all indexes in the process. Note, that moving blob data between filegroups is much harder, so this might not work out for all indexes/tables. Just an idea.

1

One important thing with shrink operation when Shrinking through management studio is the option 'Release unused space' what this does is

''Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. ''

You should try this option since there is no page movement chances of fragmentation is very less and this option can be used if there is free space in database.

Its good that you are aware about drawbacks of shrinking I am sure recommendation by Paul, Brent and Gail are more in terms of educating user about drawbacks of shrinking than stopping them. IF you want to reclaim space you have to shrink at some point and its fact that shrinking causes fragmentation so you have to deal with it. Its not like nobody uses database shrink people do use but don't recommend on public forum because they are not aware about their surrounding and impact it will make.

I would say if you know about growth about database and know in coming year space would not be utilized shrink it cautiously may be in small chunks check out option which I pointed see if it works

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.