Tuesday, June 20, 2006

DBCC SHRINKFILE EMPTYFILE

Someone asked me how he could get a file to accept new extents after he had executed a SHRINKFILE with the EMPTYFILE option on the wrong file. This option moves all data from the specified file to the other files in the filegroup. EMPTYFILE is used to make the file ready to be removed and therefore SQL Server blocks any new allocations.

The solution is actually very simple:

ALTER DATABASE myDB SET OFFLINE
GO
ALTER DATABASE myDB SET ONLINE
GO