Cannot delete data file

  • Kedar Potdar

    Old Hand

    Points: 312

    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

     

     

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Try after deatching the DB




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

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    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'

  • baseLogiK

    Valued Member

    Points: 67

    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.

  • jesse.macnett

    SSC Enthusiast

    Points: 185

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

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply