DBCC SHRINKFILE not working…

Suppose you have a really large database and changes have been made by development so that the application is more efficient with the way it stores the data. Sufficient time has passed that all the old inefficiently stored data has been purged and you want to get some of that disk space back. You run DBCC Shrinkfile but your database isn’t shrinking. Well, if you have a lot of LOB data, it won’t so, how can you get that empty space out of your database?

1. Locate the tables that have lots of empty space
2. SELECT INTO newtable
3. DROP oldtable
4. Rename newtable to oldtable name

Now the DBCC Shrinkfile will release that free space back to the operating system

*Shrinking the database causes massive index fragmentation. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s