DBCC CHECKDB behavior when the SQL Server database is located on an ReFS volume

View products that this article applies to.

Summary

In Microsoft SQL Server 2012 or an earlier version of SQL Server, you may encounter error messages when you run the DBCC CHECKDB command for a database that has its files located on an ReFS-formatted volume. The error messages resemble the following:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

If there are no users connected to the database, the DBCC CHECKDB command will obtain exclusive lock against the database and then perform the checks against the database, instead of using the internal snapshot that it typically uses.

This problem occurs because SQL Server cannot create the internal snapshot database by using named streams when the database files are located on an ReFS volume.

This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL Server, the DBCC CHECKDB command does not use named streams to create the internal snapshot database.

Notes

The information in this article applies to all the consistency check commands in the DBCC CHECKDB family (DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOG).
ReFS means Resilient File System, and it was introduced in Windows Server 2012.

↑ Back to the top

More Information

The behavior of DBCC CHECKDB commands in versions of SQL Server from Microsoft SQL Server 2005 to SQL Server 2012

DBCC CHECKDB creates an internal snapshot database.
The internal snapshot database is created by using named streams within the physical database files.
- Consider a database that has the three files E:\Data\my_DB.mdf, E:\Data\my_DB.ndf, and E:\Data\my_DB.ldf.
- Consider that the internal snapshot database is created with database ID 10.
- The internal snapshot database will be created by using the files E:\Data\my_DB.mdf:MSSQL_DBCC10 and E:\Data\my_DB.ndf:MSSQL_DBCC10.
- Notice that the named stream is created using the format <filename.extension>:MSSQL_DBCC<database_id_of_snapshot>.
- You will encounter an error if you try to create a named stream for a file that resides on an ReFS volume. However, this error does not occur if the file resides on an NTFS volume.
The named streams are marked as sparse at the file system level.
- The "Size on Disk" used by the named stream will increase based on how much data is updated in the source database during the DBCC CHECKDB command.
- The "Size" of the named stream will be the same file as the .mdf or .ndf file.
The named streams are deleted at the end of DBCC CHECKDB processing.
The named streams are not visible by using ordinary file utilities such as Windows Explorer.

The behavior of DBCC CHECKDB commands beginning with SQL Server 2014

DBCC CHECKDB creates an internal snapshot database.
The internal snapshot database is created by using physical database files.
- Consider a database that has the three files E:\Data\my_DB.mdf, E:\Data\my_DB.ndf, and E:\Data\my_DB.ldf.
- Consider that the internal snapshot database is created with database ID 10.
- The internal snapshot database will be created by using the files E:\Data\my_DB.mdf_MSSQL_DBCC10 and E:\Data\my_DB.ndf_MSSQL_DBCC10.
- Notice that new files are created in the same folder with the naming convention <filename.extension>_MSSQL_DBCC<database_id_of_snapshot>.
The new files are marked as sparse at the file system level.
- The "Size on Disk" used by the new files will increase based on how much data is updated in the source database during the DBCC CHECKDB command.
- The "Size" of the new files will be the same file as the .mdf or .ndf file.
The new files are deleted at the end of DBCC CHECKDB processing.
- These additional files that are created by DBCC CHECKDB have the "Delete on Close" attributes set.
- If the operating system encounters an unexpected shutdown while the DBCC CHECKDB command is in progress, then these files will not be cleaned up. They will accumulate space, and potentially will prevent future DBCC CHECKDB executions from completing correctly. In that case, you can delete these new files after you confirm that there is no DBCC CHECKDB command currently being executed.
The new files are visible by using ordinary file utilities such as Windows Explorer.