NDF_MSSQL_DBCC24 FILE TYPE

Yashwant Vishwakarma 116 Reputation points
2020-09-25T07:15:42.093+00:00

Dear Folks,
I am seeing one file type along with my mdf and ndf file type which is MDF_MSSQL_DBCC24 and NDF_MSSQL_DBCC24

these filetypes are having same size of mdf and ndf file, these files are created for each and every database

I am seeing these filetypes for the first time and on Internet also there is no information regarding this, hence I posted this question here

I want to know why these files are created and is it safe to delete these files
28302-ndf-dbcc24.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
0 comments
Accepted answer
  1. Olaf Helper 40,656 Reputation points
    2020-09-25T07:31:44.43+00:00

    That database files gets create as database snapshot when you issue a DBCC CHECKDB (Transact-SQL); many DBA have a maintenance to check DB integrity frequently. As soon as the CheckDB command finish the files get deleted automatically. Means the CheckDB job is still running, the files are in access by SQL Server and you can't delete the files.

    Check if the job is still runing.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yashwant Vishwakarma 116 Reputation points
    2020-09-25T07:50:49.697+00:00

    Thanks Olaf

    You are absolutely right
    DBCC job is running, I checked the job history, as per job history it should be completed shortly.

    Thanks for quick and accurate answer. :)

    You are a rockstar DBA :)

  2. Shashank Singh 6,246 Reputation points
    2020-09-25T07:54:16.513+00:00

    I believe what you are seeing may be a bug. What is output of select @@version ? If you have RTM version of SQl Server 2014 then this was a bug as mentioned in this support article.
    The article also says that

    This problem occurs if Windows is restarted before the DBCC CHECKDB command can close the file handles. In this situation, the operating system cannot clean up the temporary files that were created by DBCC CHECKDB, and these files continue to occupy space.

    Is this the case ? Was windows unexpectedly restarted.

    And regarding the strange filed with DBCCXX you are seeing again [This Support article][2] has the explanation

    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 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. [2]: https://support.microsoft.com/en-ae/help/2974455/dbcc-checkdb-behavior-when-the-sql-server-database-is-located-on-an-reConsider a database that has the three files E:\Data\my_DB.mdf,
    0 comments