We have a database that consists of 3 data files and a log file on different physical drives. Due to a change in the nature of the data, we have a large amount of free space available now and we want to drop 2 of the data files and be left with just the original data file that was created. The other files were added over time as the data grew.
The details of the file to be dropped is as follows:
Currently allocated: 127253.06 MB
Free Space : 127250.25 MB (99%)
There is more than enough space available in the other files to accommodate the small amount of data left in the file we want to drop.
When I try a shrink through DBCC with the following command:
DBCC SHRINKFILE (N'fiAuditData Exp2' , EMPTYFILE)
I get the result:
DBCC SHRINKFILE: Moving page 4:16288391 failed.
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "fiAuditData Exp2" to other places to complete the EMPTYFILE operation.
It looks like there remains some housekeeping data in the file that prevents it from being dropped but I'm not sure. I have searched many other articles but have not found a reply that allowed me to successfully drop the table after shrinking it.
Any ideas how to go about getting rid of this data file would be greatly apperciated as the SHRINKFILE with the EMPTYFILE obviously does not work as intended.