Another day of THE DBA chronicles is upon us. This time we are faced with the error 5170 that caused a database integrity maintenance plan to fail. Luckily, we set up logging for the plan and a look at the logs showed this detail message:
A database snapshot cannot be created because it failed to start.
Cannot create file ‘E:\Data\BestDatabase_Data.mdf_MSSQL_DBCC92’ because it already exists. Change the file path or the file name, and retry the operation.
The error message is telling us that it is attempting to create a file in the location ‘E:\Data’ and it has encountered that the file already exists.
Further log analysis from the SQL Server Error Log shows below:
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 ‘E:\Data\BestDatabase_Data.mdf_MSSQL_DBCC92’ because it already exists. Change the file path or the file name, and retry the operation.
Microsoft’s provisioning of additional details and suggested solutions to the error can, unfortunately, be a bit misleading. Therefore, the focus should not be put on these suggestions as it may not apply to this case. In this scenario, you can’t define the physical database snapshot file name when running a Check Database Integrity task.
A dive into the system logs should that the server experienced a system reboot during the run of the DBCC CHECKDB task. What could have caused the server to shut down? Even though this is SQL related and some administrators would not bother to go down this path, it is important you know the root cause and resolve it to prevent any future occurrences. The logs showed that a Windows update was installed which required a server reboot.
Well, that was easy to find! How do we resolve that? A few suggestions if you are caught up in this type of issue.
- Disable Windows Update
- Download Windows Update but Manually Install (Download Only)
- Set Work Hours of the server to prevent automatic server reboots from occurring.
Next, we will see how the DBA comes in to resolve the error of 5170
Find the existing file
- Using file explorer, navigate to the location of the existing file. In my case, it would be the ‘E:\Data\’. Yes indeed, there was the file that was causing the duplicate conflict during the snapshot creation of the DBCC CHECKDB task.
Fix the error
- The fix is as simple as deleting the existing file. However, as all IT professionals know, deletion is a command that is frowned upon. It’s better to rename or move the file to have a backup should anything go wrong or need to restore to the previous state.
- I also compel that you perform a certain task that I will detail below to make sure that the file is not referenced anywhere else. The least you want is to fix this error and create other issues with other processes or system relying on that file.
- The snapshot file can be inspected on the metadata through PowerShell using the CmdLet
Get-Item -LiteralPath 'E:\Data\MyDatabase_Data.mdf_MSSQL_DBCC92'
- As I found no other reference on this error, I deemed it safe to move it to another location as a backup. I could have deleted or renamed the file as well.
- How do we know it issue is resolved? We know that a database integrity check kept failing with the error 5170. The best way to test is to run an ad-hoc integrity check and it successfully completes, then we can say the error is resolved.
DBCC CHECKDB ('BestDatabase');
- The test run of the database was successful as shown below:
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘MyDatabase’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, there you have it! Another day of THE DBA made simple by providing a solution to anyone that encounters the error 5170. Now let’s say you find yourself in a situation where a proper DR plan was not established and the solution provided did not fix the issue, then you may consider looking into a third-party database repair tool. Your solution here may be heavily reliant to this third party tool since the SQL out-of-box repair tool (DBCC CHECKDB) is erroring out.
Remember that the repair feature of SQL Server is not robust and not a guaranteed solution. For a faster, more versatile repair that would bring your SQL corrupt database back into a working start with minimal data loss, then look no further with SQL Database Recovery tool. It repairs faster using a more sophisticated repair algorithm. It even can recover deleted data in your database. Click on the link to learn more.