SQL Server Portal

DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options is one of the error messages shipped with SQL Server 2012. It happens due to the new feature File table (File stream) introduced in SQL Server 2012. Lets explore this error message.
Message Number: 2563
Severity : 16
Error Message: DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options.

Error Generation:
This error message looks as if something is wrong with the “DBCC SHRINKFILE”. Lets generate this error step by step.
Step 1 :
First of all create a database having File stream.

CREATE DATABASE [MyFileTableDB]
ON PRIMARY
( NAME = N'MyFileTableDB', FILENAME = N'C:\DATA\MyFileTableDB.mdf'),
FILEGROUP MyFileGroup CONTAINS FILESTREAM (NAME = MyFileTableFS,FILENAME='C:\Data\MyFileTable')
LOG ON
( NAME = N'MyFileTableDB_log', FILENAME = N'C:\DATA\MyFileTableDB_log.ldf')
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyFileTableDB' )
GO

Step 2 :
Shrink the data file.

-- Shrink the data file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableDB' , 0, TRUNCATEONLY)

Step 3 :
Shrink the log file.

-- Shrink the log file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableDB_log' , 0, TRUNCATEONLY)

Step 4 :
Shrink the filestream file.

-- Shrink the FileStream file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableFS' , 0, TRUNCATEONLY)

Oooops……………
Msg 2563, Level 16, State 1, Line 1
DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options.

Resolution:
You cannot shrink FileStream files like data file or log file. The correct syntax to shrink file stream is given below :

DBCC SHRINKFILE (N'MyFileTableFS' , EMPTYFILE)

Conclusion:
Remember, whenever you shrink the FILESTREAM files follow the exact syntax given above to avoid this error.