New client, new idea and a new blog! That’s how I have been writing a daily blog for many years now. In this blog, I would share my learning from a client who faced error “xp_create_subdir() returned error 183, ‘Cannot create a file when that file already exists” while running maintenance plan.
This was a newly deployed server where they were having a performance issue. They hired me for Comprehensive Database Performance Health Check. Along with many other issues, I also found that they don’t have regular backups of the system databases (master, model, and msdb). We discovered that even though they had a maintenance plan created, but it was failing. I checked the maintenance plan logs and SQL Agent job logs and found that maintenance plan was failing due to below errors.
Here is the Error message from the highlighted section.
Executing the query “EXECUTE master.dbo.xp_create_subdir N’F:\\Backup\\ma…” failed with the following error: “xp_create_subdir() returned error 183, ‘Cannot create a file when that file already exists.'”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Then I used “View T-SQL” hyperlink to dig further and found that it creates a directory for each database and then takes backup. Here is the command which I found was failing.
EXECUTE master.dbo.xp_create_subdir N'F:\Backup\master'
When I looked at the folder, I found that there were files with the same name as the database name.
and that’s the cause of the error. You would get the same error if you try to create a folder in windows with the same name.
As we understood now, we already have a file with the same name which SQL Server wants to create a folder. In my client’s situation, they moved a database from old production server to new production server. So, these were the backups taken from the previous server. We renamed them and provided extension as .bak and some meaningful name. Fired maintenance plan again and backups were taken successfully.
In short, if you get this error, check if you already have a file with the database name in the folder. If yes, do something to resolve the conflict. Either rename or move the file somewhere.
Reference: Pinal Dave (https://blog.sqlauthority.com)