SQL SERVER – Error Msg 5042, Level 16 – The File ‘tempdev02’ Cannot be Removed Because it is Not Empty

As a SQL Server DBA you would know that sometimes it is possible to make mistakes with the database. Few of the time it can be corrected. Out of them, some are easy and some are difficult. I was running out of space on my VM and I started hunting for a bigger file on my machine. It was not difficult to find and recall that I added some files to the TempDB database while doing some testing. Here is the command to get details about the database file, which gave me Error Msg 5042.

sp_helpdb tempdb

When I tried to remove the file via the UI and got below error.

SQL SERVER - Error Msg 5042, Level 16 – The File 'tempdev02' Cannot be Removed Because it is Not Empty remove-fail-01

Also tried via T-SQL below

USE [tempdb]
GO
ALTER DATABASE [tempdb]  REMOVE FILE [tempdev02]
GO

And got the same error

Msg 5042, Level 16, State 1, Line 8
The file ‘tempdev02’ cannot be removed because it is not empty.

SQL SERVER - Error Msg 5042, Level 16 – The File 'tempdev02' Cannot be Removed Because it is Not Empty remove-fail-02

This was not a production environment so it was easy for me to restart, but looking at the message, it looks like it would work, if I make the file empty. So I ran below command

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev02', EMPTYFILE)
GO

It was successful and then I was able to remove the file.

SQL SERVER - Error Msg 5042, Level 16 – The File 'tempdev02' Cannot be Removed Because it is Not Empty remove-fail-03

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – FIX: Setup Was Not Able to Access Domain
Next Post
SQL SERVER – Unable to Start SQL Server – TDSSNIClient Initialization Failed with error 0xd

Related Posts

7 Comments. Leave new

  • Thanks for the posting. I’m still puzzled though:
    When I run the DBCC command, I get
    “File ID 1 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty.”
    If I then try to run the ALTER DATABASE REMOVE FILE [fstmslive] statement, I get:
    “The file ‘fstmslive’ cannot be removed because it is not empty”.

    Any suggestions?

    Reply
  • Felipe Zuñiga M
    March 23, 2017 10:13 pm

    Muchas gracias por la información, me sirvió mucho.

    Reply
  • The extra following line worked for me: !note, this was needed for a test server:

    USE tempdb;
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DBCC FREEPROCCACHE
    GO
    DBCC FREESESSIONCACHE
    GO
    DBCC FREESYSTEMCACHE ( ‘ALL’)
    GO
    DBCC SHRINKFILE (‘tempdev2’, 1024)
    GO
    DBCC SHRINKFILE(‘tempdev2’, EMPTYFILE)
    GO
    USE master;
    GO
    ALTER DATABASE tempdb
    REMOVE FILE tempdev2;

    hope it helps

    Reply
  • This worked for me on a SQL Server 2005. Thanks Pinal!

    Reply
    • I ran the emptyfile command and it ran with no errors, but I still cannot remove the file “because it is not empty”. Any idea how to fix that? This thread seems to think the emptyfile command is the golden ticket to the final solution, but that is apparently not the case….

      Reply
  • Don’t work with Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) – 14.0.3045.24 (X64) Oct 18 2018 23:11:05 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

    DBCC SHRINKFILE (temp9, EMPTYFILE);
    GO
    /*
    DBCC SHRINKFILE: Page 11:17471088 could not be moved because it is a work table page.
    Msg 2555, Level 16, State 1, Line 3
    Cannot move all contents of file “temp9” to other places to complete the emptyfile operation.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    */

    Reply

Leave a Reply

Menu