I am in the process of purging data from a multi-terabyte database which only has two data files on the PRIMARY file group.  I expect to free up at least 50% of space within the data files.  I need to reclaim that space and am looking for the most efficient way to do it.

The tables are a combination of heaps and clustered tables.  I am aware of the fragmentation issue by using the shrink command and will handle that separately if needed.  Unfortunately, I prefer not to create a secondary file group and move each table individually.  However, the shrink command either reorganizing or empty file options are running 16+ hours.

What is the recommended solution for this scenario?

• Edited by J I M B O Thursday, August 20, 2015 2:39 PM

Shrink is slow, that's to be expected, but it should not be THAT slow, unless your IO subsystem is completely under water... From time to time we have the need to shrink as well, but 500 GB are normally done within 12 hours or so on our IO bus, so that's not too big of a deal... If your system is slower than that I would be interested to see your disk performance counters while you are shrinking...

These servers are hosted in the cloud.  The disk rate is about 12 MB/s on average.  The disk IO is just odd in general.  Activity within SQL Server generally tops out around 25 MB/s per thread or so.  I don't really understand why.  I've dumped data into a heap in an empty data file with more than enough space, and it tops out around 25 MB/s.  However, file transfers to and from the servers top out at 250 MB/s.

Well, to quote Adam Savage: "There's your problem"...

Do you see details? Increased latency? Disk queue? There will be little you can do in the cloud, that's the downside of the cloud after all, but I would still be interested to see where they throttle you down.

We raised this issue with our cloud vendor a couple of weeks after I got here.  It was the typical vendor response "Everything looks fine on our end.  If you need us to investigate further, it will be a separate fee."  When looking at the resource monitor, everything looks great.  It just doesn't perform that way.

What do the PerfMon counters say? Avg Disc Seconds / Read (and Write), Current Disk Queue length, etc

The write queue length averages around 0.3.  The read queue length averages around 0.1.  Disk writes averages 11 MB/s while read is 1 MB/s.  Cache fault averages 0.1.

And how does the latency behave? (Avg. Disc Seconds/Write)

Averages 0.2 on a 1000 scale.

Weird... The values look more or less OK for an average class VM... Something is terribly wrong in the system you have here. It's a pity that you don't have access to the physical infrastructure, I would have loved to see the details...

Sorry Jimbo, but I see little you can do as long as you can't get a faster disk system in that cloud.

Yup, the system in general looks fine by the numbers.  I have no idea why it doesn't perform that way.  Thanks for taking a look into it though Prince.