Post reply

Cannot delete data file

  • July 13, 2006 at 3:35 pm

    #168972

    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

    #649144

    Try after deatching the DB




    My Blog: http://dineshasanka.spaces.live.com/

  • July 14, 2006 at 7:03 am

    #649229

    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

    #1091888

    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

    #1841706

    Thanks man! Old post, but the DBCC/EmptyFile was exactly what I needed.

  • November 26, 2015 at 10:12 pm

    #1842567

    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