July 13, 2006 at 3:35 pm
I have a database with 8 data files. I ran a DBCC Shrinkfile with emptyfile option on one file (90GB size) which ran successfully. But when I try to delete the file now, it throws the following error:
Error 5042: The file 'data8' cannot be removed because it is not empty.
I tried the same thing with other smaller data files and I could easily delete those files.
Even when I try to shrink the file, the file size remains same.
Please help!! I need to urgently get the database size reduced.
Thanks,
Kedar
July 14, 2006 at 12:10 am
Try after deatching the DB
My Blog:
July 14, 2006 at 7:03 am
Kedar
Run this script, after inserting your logical file name in both places (the logical file name is what you see under File Name in the Data Files tab of the Properties box of the database). If it returns any items, then you need to move the tables/indexes to a different filegroup before proceeding.
John
SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'My_Logical_File_Name'
UNION
SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'My_Logical_File_Name'
December 14, 2009 at 5:57 am
I had the same problem and tried the aforementioned query graciously provided by John Mitchell-245523, but found no objects listed. I ended up running DBCC SHRINKFILE('logical_file_name', EMPTYFILE). After that, the file dropped with no problems.
November 23, 2015 at 7:06 am
Thanks man! Old post, but the DBCC/EmptyFile was exactly what I needed.
November 26, 2015 at 10:12 pm
Use the DBCC SHRINKFILE with the EMPTYFILE argument command
dbcc ShrinkFile (mydatabase_Log_2, EmptyFile)
or you can take the help of this link: http://www.sqlservercentral.com/blogs/jeffrey_yao/2009/08/10/error-5042-cannot-remove-a-file-because-it-is-not-empty/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply