SQL Server – Should the Database Be Shrunk?

shrinksql-server-2008-r2sql-server-2012

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.

Best Answer

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.

Related Question