Have you noticed an interesting behavior with SQL Server 2014 when you are running a dbcc checkdb command? If not, you should have a look at this blog post. I discovered it completely by accident during some tests. If you watch carefully, you will see “weird” files appear while the dbcc checkdb command is running.
Below is a sample of the file you will find near the corresponded database file:
The file immediately disappears after completion of the dbcc checkdb command. So the question is, what is this file? The type of this file is MDF_MSSQL_DBCC10 and we can easily guess that it concerns a dbcc checkdb command, but I must admit I have never seen it in the past…
To be sure, I decided to trace the file activity of the SQL Server process while the dbcc checkdb command is running. You can see the part of the result that concerns our investigation below:
So we can now say that these files concern the dbcc checkdb activity and Kevin Farlee (SQL Program Manager at Microsoft) confirmed that this new behavior is quite normal.
Here is the explanation:
Prior to SQL Server 2014, the files for dbcc checkdb were created as alternate data streams of the regular database files. This is why we don’t see them by default (alternate data streams can be showed by using the dir command with /R parameter). But with the release of Windows Server 2012 and the new ReFS file system, the story has changed because it does not provide any more alternate data streams capabilities, which is why it was not compatible with SQL Server prior to the SQL Server 2014 version (see KB2681562).
This is a good news, because as you certainly know, ReFS provides some enhancements over NTFS. This is a more resilient file system and it has a better support for extremely large amounts of data. As a reminder, with large databases, you may face the operating system error numbers 1450 and 665 when you run dbcc checkdb command due to a NTFS limitation. These errors are reported in Microsoft KB2002606 and some fixes or workarounds are fortunately available.
Time to switch to ReFS file system with SQL Server 2014? Your thought will be appreciated!
By David Barbarin