1

I have a mdf file that is not attached but when I try to delete it Windows Explorer fails due to the file being in use. SQL Manager also says it's in use when I try to attach the MDF so that I can delete it. Is there a way to kill the connection and delete the file without stopping the SQL service? Thanks

10
  • 1
    Sounds like you might have more than one instance of SQL Server installed, or the database is attached as a name that is not obvious from the file name. Apr 23, 2020 at 19:03
  • 1
    You need to find out what process has a handle to the file. learn.microsoft.com/en-us/sysinternals/downloads/… Apr 23, 2020 at 19:06
  • I have looked and there is only one instance of sql 2014 installed on the box.
    – PBK
    Apr 24, 2020 at 11:19
  • I have tried using process explorer and can't figure out how to use it to find what is using the db. Searching for a db name doesn't result in anything. Even searching for functioning db's doesn't result in anything.I have tried searching and drag and drop on the bulls eye icon.
    – PBK
    Apr 24, 2020 at 11:21
  • Strange thing is if I create a backup that database is in the selection list for databases.
    – PBK
    Apr 24, 2020 at 11:22

1 Answer 1

2

Try to set the database (returned by the query in sys.master_files) Offline with

Alter database [YourDB] set offline;

If the database is still seen in the dmv, it may still be possible to set it as offline (and then you'll be able to delete the file).

But if you want to delete the file, you could also just try to drop the database

Drop database [YourDB];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.