2015-08-18

CheckDB error because snapshot file already exists

A ordinary Maintenance Plan with a ordinary Check Database Integrity task failed with this error in the Maintenance Plan log:
A database snapshot cannot be created because it failed to start.
Cannot create file 'D:\\Data\\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

The error text indicates that the file exists which is true.
Also the text indicates that the file path or name can be changed. This is not true in this case as one can't define the physical database snapshot when running a Check Database Integrity task. This is why it can't be defined when running DBCC CHECKDB statement.
In the SQL Server Errorlog the failure is also logged:
2015-08-16 03:11:19.77 spid42      Error: 5170, Severity: 16, State: 1.
2015-08-16 03:11:19.77 spid42      Cannot create file 'D:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

As the information is similar the failure is confirmed on file details.

In this case the server had a hard shutdown (ups!!!) which caused the CheckDB failure.
To confirm if the database snapshot on the file is still active this statement:
SELECT *
FROM [master].[sys].[databases]
WHERE
  [databases].[name] = 'MyDatabase' AND
  [databases].[source_database_id] IS NOT NULL;

The result is empty which is confirmed by the message:
(0 row(s) affected)

The snapshot file can be inspected on the metadata through PowerShell using the CmdLet Get-Item:
Get-Item -LiteralPath 'E:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75'

As I found no other reference on this error I preserved the database snapshot file by moving it to another location.

Testing the solution is quite simple as I only have to run a CheckDB on the database:
DBCC CHECKDB ('MyDatabase');
As planned the run is good:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

1 comment:

Unknown said...

Thanks for sharing this post. Your post is really very helpful its students.
SQL server dba Online Training