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?