5

We use MSSQL 2016 database, I recently see a file call DatabaseName.mdf_MSSQL_DBCC15 in the same location where my database .mdf file located. It has identical file size as my .mdf database file.

Do you know what is this file and what caused it to be created? I googled around and the term snapshot comes up here and there but I can't confirm 100%.

  • 3
    This would be a file created by using a DBCC. Since 2005, DBCC creates a database snapshot to provide a stable environment to run DBCC against. The snapshot files used to be hidden until 2014. I suspect the size and size on disk to be very different where the size on disk being very small. You can recreate this situation by running DBCC against a fairly large database and watching the default database directory for these kinds of files to appear. You can search around for DBCC and snapshot files to find out more. – Scott Hodgin Oct 31 at 18:38
  • 2
    That is the snapshot file created by DBCC CHECKDB command. find more details here.. – Shekar Kola Oct 31 at 18:40
  • If this is a snap shot caused by DBCC, I wonder does SQL handle the removal of it or it stays there forever? my database file size is about 200GB so this is not a small file to let it stays around. – Fylix Oct 31 at 18:40
  • 5
    SQL Server normally deletes the snapshot files when the DBCC completes. Perhaps the process was terminated in a way that prevented the automatic cleanup of the file. You should be able to delete the file assuming no active processes are using it. – Scott Hodgin Oct 31 at 18:42
8

This would be a file created by using a DBCC.

Summarizing the pertinent information in the link that Shekar Kola provided:

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 _MSSQL_DBCC.
  • 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.
  • I'll try and restart my database at some point but it seems to stuck there for about couple of days now. At first I thought it was something I did but during all these, i was able to manually running DBCC CHECKDB and received back normal looking result on that command. – Fylix Oct 31 at 19:24
  • for those who may find this useful. I query the sysprocesses found the DBCC CHECKDB process with killed/rollback status. This has been there for the last couple of days and these prevents the snap shot file from being deleted. I tried to stop some jobs in attempt to kill these but I couldn't. I ended up restart the database and these clear out immediately along with the huge snap shot file. – Fylix Nov 4 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.