I have a database which was 11TB in size. I recently truncated over 5TB of data from this database.
(I am fully familiar with all the reasons why you would not normally shrink a database please)
I'm curious about what the DBCC SHRINKFILE command is actually doing because when I run the command to shrink a file which is about 650000MB in size with 45% free space it doesn't seem to actually move any pages around.
The code in question is:
USE [CAF] GO DBCC SHRINKFILE (N'FILENAME' , 650239) GO
When I monitor the performance metrics of the server on executing DBCC SHRINKFILE I see the following
- % Active time of the disk immediately jumps to 100%.
- The sqlservr.exe and system processes both start reading at a continuous 2MB/s
each (sometimes peaking at 5MB/s).
- The disk queue length jumps to exactly 1
- Disk iops jump to about 250 (note the disks in tests can achieve 6000iops)
CPU remains idle
Even if I tell DBCC SHRINKFILE to shrink the file by a single MB and let it run for 30 minutes, it does not complete.
Everything I've read suggests that DBCC SHRINKFILE should take pages from the end of the file and move them to free space near the beginning but even at the worst performance it should take no more than a few minutes to move 1MB worth of pages
What is DBCC SHRINKFILE actually doing, it just doesn't make sense to me?