SQL SERVER – xp_create_subdir() Returned Error 183, ‘Cannot Create a File When that File Already Exists

SQL
2 Comments

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.

THE SITUATION

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.

SQL SERVER - xp_create_subdir() Returned Error 183, 'Cannot Create a File When that File Already Exists xp_subdir-01

Here is the Error message from the highlighted section.

Solarwinds

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.

SQL SERVER - xp_create_subdir() Returned Error 183, 'Cannot Create a File When that File Already Exists xp_subdir-02

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.

SQL SERVER - xp_create_subdir() Returned Error 183, 'Cannot Create a File When that File Already Exists xp_subdir-03

WORKAROUND/SOLUTION

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.

SQL SERVER - xp_create_subdir() Returned Error 183, 'Cannot Create a File When that File Already Exists xp_subdir-04

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – FIX: Msg 15281 – SQL Server Blocked Access to STATEMENT ‘OpenRowset/OpenDatasource’ of Component ‘Ad Hoc Distributed Queries’
Next Post
Detect and Diagnose SQL Server Performance Issues with Spotlight Cloud

Related Posts

2 Comments. Leave new

Leave a Reply

Menu