I have database I am trying to attach. In SSMS the database does not show in the listing. However, the .mdf and .ldf files are shown. When I try to attach the database I get the following error:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "F:\TPTVSQL\TVDB336.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
The database name shows in sys.master_files, but does not show in sys.databases. I can path to the actual database files, but nothing I've tried has worked for attaching the database. I am a system admin with full control to the system. Any ideas?
It sounds like another database is using those database files. Check the properties of each of the databases to see if they are using the files. If they are not, I'd suspect AVS.
Actually I just noticed what you mentioned about sys.master_files. What happens if you try the DROP DATABASE command?
I I try to drop the database it tells me that it does not exist. In sys_master_files the "state_desc" says ONLINE. Also in sys.master_files I do not see any other database pointing to the .mdf and .ldf for this guy. There are no other instances running on this box.
AVS=anti-virus software
But that's not the issue here.
See if the very last reply in this thread helps:
Specifically this:
"I was having this same issue using SQL server 2008r2.
I was trying to duplicate a database (versioning foo) and ended up confusing SQL server. SQL Server Management Studio thought that the database existed (the MDF and LDF did) but the master database didn't think the database existed (didn't show up in sys.databases)
The trick was to rename the MDF and LDF files, then create the database from SQL create database [db name here] then delete the database and finally attach the MDF and LDF files from before."
But first, stop the SQL Server service and copy the files somewhere else. Then rename the files in the original location and startup the service. Create the database, then drop, which hopefully fixes the orphaned database stuff in master. Then copy the files back and try the attach. I think I have the steps correctly based on that reply in the link.
I will try this in a maintenance window.
If this is a production system, I would advise opening a support case with Microsoft. At the very least, post your question on a site where Microsoft engineers respond and see if those steps are okay to do in production. To me, it's too risky for production without further guidance from Microsoft.
Thank Tara. The previous process worked. SQL was just confused. Thanks again.