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.
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.
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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?
you need to look at sys.dm_exec_requests to see what else is running on that database.
Muchas gracias por la información, me sirvió mucho.
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
This worked for me on a SQL Server 2005. Thanks Pinal!
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….
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.
*/