Wednesday, October 24, 2012

DBCC SHRINKFILE TempDB

If you try to run DBCC SHRINKFILE command after a hung DBCC SHRINKFILE session (e.g. you cancel it in the middle of the shrink process) or you are shrinking the database in the middle of backup, you may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

Microsoft KB suggest that the error may be resolved by restarting the SQL Server service or take database offline and then put the database online again. Here is the KB link.

In some cases, rebooting the server is not an option, and should be always the last option, you can trick it (reinitialize) by increasing the data file size (e.g. 1 MB).

USE tempdb;

SELECT 
 name,
 size * 8 / 1024
FROM sys.database_files;

Find out the size of the database and add 1 MB.

ALTER DATABASE tempdb
MODIFY FILE
(NAME = 'tempdev', SIZE = <current database size + 1 MB>);

After execute the command, DBCC SHRINKFILE should works again without any error message.

And always consider twice if you really need to shrink a database file. Cause it may cause a lot of problem like fragmentation.

Google+