I have a 1.8TB Production database in my test environment on a 2TB drive. Currently there is only 1 .MDF and 1 .LDF (on a separate drive). The Database has had some major cleanup of unused indexes, Archived tables, and de-fragmented. The actual data size being used is now only 300GB (after an SP_Spaceused) of the 1.8TB container.

I have tried multiple DBCC Shrinkfiles (larger and smaller Target sizes varying from 100MB to 2GB) without success. The job never fails, but after 22 hours of running (at multiple attempts of sizes) I stopped the job. No errors, Database is in simple recovery mode, drives are not full, nothing in windows or SQL error logs. I've also tried the Truncate option that finishes successfully, but no space is released.

Why do I want to shrink you might ask? I want to migrate from 1.mdf to (1.mdf + 3.ndf) data files for improved performance (on a RAID 10 set of Drives). I don't have enough disk space on my 2TB drive now to do it all on the same RAID Set.

My thoughts were, shrinkfile (truncate) or Shrinkfile Target in smaller increments. The truncate completes successfully and SQL thought the minimum size would be around 300GB. Unfortunately, the DB only shrunk to 1.2TB initially and I believe there is more unused space that could be freed. Its like there is some kind of Ghost record or row that cannot be moved in order to reclaim that unused space.

I did run Integrity Checks with physical, indexes and data purity which reported no errors. I can shrink other databases on the same server without any troubles...just this one that is giving me trouble.

Has anyone Run into this?

  • Try dbcc cleantable on the tables that you performed truncation or rebuild indexes. Alternatively, you can create an empty database of 300gb and bcp out and then bcp in the data. – Kin Shah Jun 23 '16 at 12:32
  • 3 ndf on the same drive is not going to help performance – paparazzo Jun 23 '16 at 13:47
  • 1
    Sorry, I should have explained better...The .MDF is on a RAID 10 Set of 4 drives. We've done similar type of file striping with good success. I did find the DBCC Cleantables article...it has been running for 18 hrs so far. I hope that will cleanup any Ghost files. Thank you for your suggestions, I will let you know if it works. – G Phunk Jun 24 '16 at 13:35
  • Try dbcc cleantable on the tables that you performed truncation or rebuild indexes. This was the ultimate solution for me....It took 2 days to complete, but afterwards the free space was reclaimed. Thanks very much KIN for this idea. – G Phunk Jul 4 '16 at 11:32