DBCC - Database Integrity failing - DB snapshot cannot be created

Pds 46 Reputation points
2021-10-26T16:13:48.957+00:00

Hello,
I am running Daily Ola Maintenance job Database Integrity Check with following:
DBCC CHECKDB ([ADTDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY

Error: 

database snapshot cannot be created because it failed to start.  Msg 1823, Level 16, State 8, Server 
Msg 5170, Level 16, State 1, Server ADTSql, Line 1  Cannot create file 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8' because it already exists. Change the file path or the file name, and retry the operation

When I see this ADTt.mdf_MSSQL_DBCC8 file, it's almost 1 TB like database size.

Is it I should move this file somewhere and rerun the job or delete this file?
Is it any another option?
Keeping in mind, as DB size is almost 1 TB so normally we run during early morning but from last 2 days it's having this issue.

When I run following, I am not any data, Result is empty: 

SELECT *
FROM [master].[sys].[databases]
WHERE
  [databases].[name] = 'ADTDB' AND
  [databases].[source_database_id] IS NOT NULL
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,607 questions

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pds 46 Reputation points
    2021-10-26T16:16:00.703+00:00

    I also ran PowerShell:

    Get-Item -LiteralPath 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8' 

    Directory: D:\MSSQL12.MSSQLSERVER\MSSQL\DATA

    Mode LastWriteTime Length Name

    -a--- 10/22/2021 1:00 AM 1199994568 adt.mdf_MSSQL_DBCC8
    704

    0 comments
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-10-26T22:03:15.287+00:00

    My first reaction when I see this error is "might DBCC already be running?" But if you have verified that this is not the case, I would try to delete it.

    While the file is said to be 1TB, it does not take up that much space, as it is a sparse file and most of the data is in the original database file. Only pages that have been modified since the snapshot was taken are physically in that file.

    0 comments
  3. Pds 46 Reputation points
    2021-10-26T22:24:04.273+00:00

    Thanks Erland.
    So It's safe to delete it? It won't cause any issue in Database, right?
    What I can check to make sure it?
    This file has timestamp in server is 10/21/21 so it's from that day and we are running every night this job

    adt.mdf_MSSQL_DBCC8 ==> Size 1.09 TB ==> Size on Disk: 66 MB Date Created: 10/21/21

    Also I ran Powershell, and it shows following:

    Get-Item -LiteralPath 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8'

    Directory: D:\MSSQL12.MSSQLSERVER\MSSQL\DATA

    Mode LastWriteTime Length Name

    -a--- 10/22/2021 1:00 AM 1199994568 adt.mdf_MSSQL_DBCC8
    704

    Please let me know

    0 comments
  4. YufeiShao-msft 7,051 Reputation points
    2021-10-27T02:55:05.02+00:00

    Hi @Pds ,

    Cannot create file 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8'

    check your system logs to see if the server experienced a system reboot while runing the DBCC CHECKDB task.
    There are a few suggestions:
    disable Windows Update
    download Windows Update but manually install
    set work hours of the server to prevent automatic server reboots from occuring

    now, you have obtained the path of the file, it safe to move it to another location as a backup, deleted or renamed the file as well
    and then yu can run an ad-hoc integrity check to test if it completed successfully
    https://www.stellarsqldatabaserecovery.com/blog/fix-microsoft-sql-server-error-5170/

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  5. Pds 46 Reputation points
    2021-10-27T19:41:57.767+00:00

    Yufeishao,
    Server reboot happened but on that day but timing is somewhat not matching as we run this job early morning but looks like job runs more then 4-5 hours so during end of the job, server restarted so that might cause an issue.

    I am little confused as this file 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8' is still there but last night job ran fine no error.
    I am having issue with copying file somewhere as it shows 1 TB size and finding location to move this is right now stuff and server doesn't have enough space I can rename it as we are running job every day and it might fail again due to lack of space.
    I am also afraid to delete this file if it's still reference it?
    Is it Safe to Delete instead of moving or renaming?
    Is it Following Powershell shows fine to Delete it? 

    **Get-Item -LiteralPath 'D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\ADT.mdf_MSSQL_DBCC8'**

**Directory: D:\MSSQL12.MSSQLSERVER\MSSQL\DATA**


Mode **LastWriteTime**          **Length**                 **Name**

-a--- 10/22/2021 1:00 AM 1199994568   adt.mdf_MSSQL_DBCC8
                                                704