Home > SQL Server > SQL Server SHRINKFILE not working

SQL Server SHRINKFILE not working

As microsoft put in in SQL Server Books online, “A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation” (Note: all quotes are from SQL Server BOL)

The other day I blew away over 100GB of tables and issued the shrinkfile on both the Data and Log files and nothing happened.

Here is the reason…

If you use the SQL management tool and choose to shrink the data file releasing unused space you will get the following command executed:

DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO

When you read about “Truncate Only” it says

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

So depending on how the tables are allocated on disk inside the .mdf file it cannot release anything. Kind of like trying to partition a hard disk, you have to defrag the disk to move all the data to the front of the disk so you can make a big “contiguous” block at the end to repartition.

so really we want to essentially defrag our database file first, and then truncate, enter the “NOTRUNCATE” statement

DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO

Again SQL Server BOL says:

Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change.

Note: This is only applicable to the datafile, it does nothing on the log file
So what you really want to do is:

DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO
DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO

This will essentially defrag your .mdf file and then truncateonly will shrink down to the last allocated extent.

I may be wrong but I think that is what the ShrinkDatabase attempts to do and hence why it appears to take so long.

DBCC SHRINKDATABASE(N'YOURDBNAME')
GO

My recommendation:

  1. Try to shrink the file first and see what gets released.
  2. If its not good enough then try ShrinkDatabase.

Note: Shrinking my database file of 100GB took 41minutes on a 16 CPU box with 32GB of memory
Cheers

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: