I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode. Since we have many teams working on different projects in parallel, we have about 20 copies of this database in different environments as well as the main DEV, QA, Staging and Production copies.
For DEV, QA and other environments we do not need the full database so we developed a script to remove old data from the top 20 tables. This reclaims about 1 terabyte of free space. Across all environments we will save at least 15 terabytes of disk space on the SAN. I know this shrinking process causes index fragmentation - but don't mind rebuilding all indexes after the database has been shrunk.
We have two problems with Shrink database:
It seems to take forever; more than 48 hours.
It does not shrink the database as much as expected. The shrink process completes without error but leaves 100's of MB's of free space in the database.
I have tried:
DBCC SHRINKFILE ( 'XXXX_Data', NOTRUNCATE ) GO DBCC SHRINKFILE ( 'XXXX_Data', TRUNCATEONLY ) GO DBCC SHRINKFILE ( 'XXXX_Log' ) GO
I have also tried creating a new filegroup and moving data to it - with no result.
Has anybody had to do this type of thing before on a similar sized database? Any recommendations on how best to do this? Or does anybody know why the database shrink does not work?