asked
Cannot drop empty datafile on principal server
Hi, The following problem: I created a filegroup with one filename on a mirrored database. Database is principal, Mirror mode is "High Performance (Asynchronous)". Database is MSSQL 2005 EE, x64, SP3 CU10 This filegroup was temporary used to store deleted data, but it's no longer necessary. So all the tables are dropped. File is 99% empty (total file size = 16384MB). However, I cannot drop the datafile, getting the error message "it's not empty" (error 5042). However, If I do the same action on a recent copy of this database (with no mirroring), it works ok. The mirrored database has a snapshot. I already tried dropping the snapshot and dropping the datafile again, with no result. Any idease?
How big is the database? Is removing mirroring and dropping the datafile an option? I am not a mirroring guru by any means, but I would suspect that mirroring has the file locked. You bring up a good scenario that I need to test with but am pressed for time currently. I might be able to test this theory later this afternoon, or you may have the time to create a database, split the files, setup mirroring and then try to drop a datafile. See if you get the same error. Then remove mirroring and try again. If that works you have your solution.
Total database = 600GB, This (empty) datafile = 16GB. Removing the mirror is not an option.
The file that is empty is 16GB? Run this query and validate that the file truly is empty. SELECT a.FILEID, [FILE_SIZE_MB] = CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)), [SPACE_USED_MB] = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)), [NAME] = left(a.NAME,15), [FILENAME] = left(a.FILENAME,30) FROM dbo.sysfiles a
Yes, it's totally empty, but here's the workaround: Shrink the empty database (using "reorganize pages", not "Release ...") After the shrink has finished and you'll see the datafile has shrinked, you can drop the datafile.
