SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

Feeds:: Posts; Comments

« SQL Server – Understanding ‘Agent XPs’ Server Configuration Option

SQL Server – Transaction Log File is Full; How to Shrink the Transaction Log »

SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

October 7, 2012 by Suresh Raavi

When tried to empty one of the tempdb data file using the below T-SQL command:

(My goal here is to REMOVE the file using ALTER DATABASE command followed by. For more information on removing data/log files, please refer to this post How to Best Remove Extra TempDB Data Files and Log Files)

--To empty 'tempdev12' data file
USE tempdb
GO
DBCC SHRINKFILE (tempdev12, EMPTYFILE); 
GO

I got the below error message:

DBCC SHRINKFILE: Page 9:44720 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 1 Cannot move all contents of file “tempdev12” to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I figured out this is due to the existing connections and existing temp tables. To avoid this we can either

Kill all the existing connections to the database OR
Re-start the SQL Server

In may case it is not a critical production instance, so I just restarted the server and it worked.

For more information on how to use SHRINKFILE command please visit my previous post Transaction Log is Full – How to Shrink the Transaction Log

Posted in Files and FileGroups, Shrink | Tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 4 Comments

4 Responses

on October 21, 2014 at 4:03 am | Reply janjaapatcq

Hi,

I just want to empty a file of my tempdb. But de file is in use by myself. I can kill it, but when I run the DBCC SHRINCKFILE it start a new proces and blocks the file.

Do you have any tips?
on October 22, 2014 at 10:51 am | Reply Suresh Raavi

Can you please be more clear on what starts the new process and blocks what?
on April 3, 2015 at 1:04 am | Reply Hank Freeman

Thanks for the post
1. You cannot put the Tempdb in Single user mode, which is logical since it is a system database or sorts
2. The Restarting of your SQL Server instance prior to the execution of the REMOVE FILE command is essential
3. Here is my post from a TEST where I had added an NDF file..
Warning the TempDEV_2 file declaration got lost somehow in this process and the system thinks it is still there, which it is not.. So be careful and test your syntax on test server instance before you do what you need on a production server..
— my syntax
use tempdb
go
sp_helpfile
————-
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev_4′, FILENAME = N’V:\Instance 7\tempdb_4.ndf’, SIZE = 100MB, FILEGROWTH = 0% ) TO FILEGROUP [PRIMARY];
GO
DBCC SHRINKFILE (N’tempdev_4′ , 0) –shrink to 000 MB
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev_4′, SIZE = 0KB, FILEGROWTH = 0% );
GO
— The SQL Server Instance MAY have to be Restarted to clear all system usage pointers into the Tempdb database
— before this command can be performed
ALTER DATABASE [tempdb] REMOVE FILE [tempdev_4];
GO
sp_helpfile

HFreeman@msn.com
end…
on August 11, 2017 at 11:39 am | Reply Jay Montavon

Please note that restarting still didn’t free up what was causing the dbcc ’emptyfile’ to fail. I had to run DBCC FREEPROCCACHE, and then I was able to remove the extra tempdb files.