DBCC Job Failed with Error “Cannot create file because it already exists” – Solved

When running DBCC checks on a database the process is actually carried out on a snapshot of the database.

This works well and helps to ensure that there are no problems with blocking or transaction consistency.

The problem is that if your server gets restarted whilst DBCC checks are in progress files can get left behind.

In cases like this, you will get an error like.

Cannot create file ‘XX_xxx.mdf_MSSQL_DBCC70’ because it already exists. Change the file path or the file name, and retry the operation.

Image: UnSplash

The process to fix this is as follows.

Check to ensure that the snapshot is no longer active.

The below query checks for any snapshots. Go through any results carefully to make sure the relevant one is not present.


SELECT *
 FROM [master].[sys].[databases]
 WHERE
   [databases].[source_database_id] IS NOT NULL;
   
   

If none exist then you can remove the files being careful to only delete the snapshot files. The files will actually report that they are the same size as your data files, but in reality will take up next to no space at all.

This issue has happened to me a couple of times and each time it is because the servers have been restarted or had failovers, whilst the DBCC job is running.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply