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
- Try to shrink the file first and see what gets released.
- 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
Recently I had the same problem trying to clear up a 130GB database. I found another quick but possibly dodgey approach.
I could not get the log file to shrink. I backed up the log file, shrunk and tried everything and it would not shrink.
I then changed the database from full to simple logging, and shunk the log file to 1MB in about 2 seconds.