Monday, May 16, 2011

Can not delete the mdf and ldf files after detaching

I saw several people have reported this issue in forums. The probelm is when you detach a database from SQL Server and then try to delete the mdf and ldf files you get the following error
"Make sure the disk is not full or write protected and that the file is not currently in use"

So the error message is can be misleading as you tend to think it is a space issue, well it can be sometimes but most of the times it is not.

First you much check whether the file is actually in use by SQL Server. You can run below command to get that information

SELECT * FROM sys.Master_Files WHERE physical_name = 'Path To Your File'

 
If there are no records as I have seen in several cases, the issue's root cause is file ownership is not assigned for the logged in user. As I have seen most of the cases files owned by the SQL Server service account as well as the user account that restored/ created the database.

To fix this you have to get the ownership of the file. You can do this by right clicking the file go to properties and then going to security tab. On the security tab click on advanced and then go to owner tab. There you can change the ownership either to your logged in account or to a group which you have rights to.

This should solve your issue and then you can delete the files. Please drop a comment if you come across any issues, I am always here to help.

No comments:

Post a Comment