question

Wilfred van Dijk avatar image
Wilfred van Dijk 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?
database-mirroringdropdatafiles
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Total database = 600GB, This (empty) datafile = 16GB. Removing the mirror is not an option.
0 Likes 0 ·
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
0 Likes 0 ·
Awesome. Glad you got it figure out.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.