0

For years I've had it drilled in to me:

Never ever shrink a database file.

I understand why of course, and I never do, mainly because I never need to.

Now though, I have a database with an .mdf file that is 800GB. It was a system designed to collect data over a certain amount of time, with no retention period.

A retention period of 90 days was recently placed on this data, and as such the developer has cleared about 3 years worth of data from the tables. Data and Indexes now total roughly 70GB, so now I have an .mdf file which is grossly over-sized.

I want to shrink it to reclaim some of that valuable disk space.

I'm planning to perform a shrink prior to rebuilding indexes and updating statistics (i.e the weekly maint. plan)

I'm not breaking any DBA laws here am I? I assume this is an acceptable scenario in which to perform a SHRINK as it is a true one off?

Thanks

Molenpad
  • 1,814
  • 2
  • 23
  • 41

2 Answers2

3

This should answer your question : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Quoted:

So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?

The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the

tables and remove fragmentation from them at the same time Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.

If you absolutely have no choice and have to run a data file shrink operation, be aware that you’re going to cause index fragmentation and you should take steps to remove it afterwards if it’s going to cause performance problems. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.

So if you have multiple filegroups you can use this technique, otherwise you can make a shrink and then do a rebuild of your indexes.

Stijn Wynants
  • 1,792
  • 12
  • 18
0

Yes it is absolutely fine to do this. I am sure some people will come online and say that of course you shouldn't, it's evil etc etc but it is fine.

Think for yourself :)

Mat
  • 544
  • 2
  • 4