I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to shrink this down to 50GB. I know that shrinking database files is bad, it causes fragmentation etc. As part of the data purge/shrink we also have a rebuild idnex script.

When I run my dbcc shrinkfile script against the database on my own workstation (quad core, 12GB RAM, 2 x SATA drives), the shrink takes around 8-10 minutes.

When running the identical code against an identical copy of the database post data purge, in our testing envrionment, (80+ cores, 128GB RAM, SSD SAN), it takes 70 minutes. To note, there is little activity on this server at the time of the shrink file running. It has been run 4 times with identical results.

I then took a different approach, of moving the remaining 9GB to a different filegroup and physical file. Running dbcc shrinkfile on the empty 230GB file to shrink it down to 50GB, on my own workstation takes < 1 minute.

With this same approach, on the testing environment, it again takes 70+ minutes.

I have taken a snapshot of waitstats before and after as per Brent Ozar's script during the 70 minute run on the test environemnt, and the waittypes returned showing nothing to be conerned about. Top 3 rows below:

Second Sample Time Sample Duration in Seconds  wait_type   Wait Time (Seconds) Number of Waits Avg ms Per Wait
2013-05-28 11:24:22.893 3600    WRITELOG    160.8   143066  1.1
2013-05-28 11:24:22.893 3600    CXPACKET    20.9    13915   1.5
2013-05-28 11:24:22.893 3600    PAGELATCH_EX    11.1    443038  0.0

Windows event log shows nothing unusual. I am heading scratching at this point, why it is taking so long on the ninja hardware, compared to my standalone workstation.

  • Checkpoint the database and then try it.
    – Kin Shah
    Commented May 28, 2013 at 17:01
  • clear the latch stats before the shrink and capture them after the shrnik, on both machines. sys.dm_os_latch_stats Commented May 29, 2013 at 8:25
  • Also, @@version on both Commented May 29, 2013 at 8:26
  • Was the data that was deleted blob data or normal data? The copy on your workstation was it deleted there or did you backup the QA system post delete then restore it to your machine?
    – mrdenny
    Commented May 29, 2013 at 17:54

1 Answer 1


Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer.

So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores.

Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.