1

My database file Whist.mdf was attached to a SQL Server Express through Management Studio, but it stopped working in that sense that NHibernate in my asp.net solution fired some connection exceptions. I just detached it from Management Studio because I couldn't open the database in there either.

But when I try to reattach it I get this error:

Attach database failed for Server 'MyPCName\SQLECPRESS'. (Microsoft.SqlServer.Smo) Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Whist.mdf". Operating system error 32: "32(process cannot access the file because it is being used by another process)". (Microsoft SQL Server, Error: 5120)

I have googled it but can't find a solution that works for me. E.g.:

There is something about User Instance but I cant figure out if it have anything to do with that:

I have tried to delete the mdf file in windows but I can't do that either:

The action can't be completed because the file is open in system

I have tried stopping the SQL Server process, closing explore and delete from cmd and rebooting...

Hope someone can help - I guess it is just a simple thing..

Community
  • 1
  • 1
Morten Holmgaard
  • 7,484
  • 8
  • 63
  • 85

3 Answers3

4

I think you are on the right track. The error messages you are getting indicate that some SQL Server process is keeping the database file open. And SQL Server is designed to only allow the database file to be open by a single SQL Server process at a time.

You are also probably right to suspect User Instance of SQL Server Express to be the guilty process here. User Instance is a special SQL Server process that doesn't run as a service and instead is started and owned by the application that tries to connect to it. In your case there are two User Instances that can be the problem, one owned by the ASP.NET application you mentioned and one owned by you and started by Visual Studio. If you are using IIS Express or you configured the IIS App Pool for the ASP.NET application to run as your account then we only have one User Instance to worry about.

My recommendation is that you don't use User Instances and don't try to connect directly to the database file. Go over your connection strings and remove User Instance=true from all of them. You want to remove AttachDBFilename=... property as well.

If the database file is added to any of your Visual Studio solutions remove it (just be careful not to delete the file itself). The last step is to remove any connections to the database file from your Data Connections node in the Server Explorer.

Then restart the machine to make sure any User Instances that were running in the background were shutdown. After the machine starts again you should be able to attach the database file back to your main SQL Server Express using SQL Server Management studio. Let's name the database Whist

To connect to your database use the following connection string:

Data Source=.\SQLExpress;Integrated Security=true;Initial Catalog=Whist

When you connect from the ASP.NET application, make sure that you have created an account in your SQL Server Express for the application.

Krzysztof Kozielczyk
  • 5,887
  • 37
  • 28
  • I have tried to follow your suggestions - this is what I have done: Can't figure out how the ASP.NET application in my case should be holding an instance because I'm just using VS internal server to run my application(the problem is in my dev environment). De mdf file isn't included in any solution or there isn't any data connections from VS to the database. ConnectionString match yours. I have disabled user instance in management studio with this: `sp_configure 'user instances enabled', '0'` I have restarted multiple times and tried stop/start sql server - no change.. Anything I'm missing? – Morten Holmgaard Feb 12 '12 at 20:07
  • One thing you could try is to set SQL Server service into **Disabled** state and restart. This should prevent it from being started or starting any User instances. At this point you should be able to move/rename your database file (don't forget about the *.ldf files). If not then something **really** strange is happening, maybe something as silly as anti-virus blocking the file? After you enable SQL Server again you should be able to attach the database again, maybe at first under a different name and do some cleanup from SSMS. – Krzysztof Kozielczyk Feb 13 '12 at 18:50
  • This didn't work either... I've no clue.. I'm considering reinstalling SQL Server - do you have any other ideas? – Morten Holmgaard Feb 13 '12 at 20:04
  • No idea whatsoever, as long as you are sure this is the only SQL Server instance on this machine. – Krzysztof Kozielczyk Feb 13 '12 at 22:36
  • After removing Sql server it still no change... Until I accidently double clicked the mdf file and it mounded to deamon tools(somehow the mdf file is associated with it) and it was not hard to figure out that I properly have done something similar to start the problem.. I unmounded it and no problems.. And the deamon tools association will be removed! Thx for you help.. – Morten Holmgaard Feb 17 '12 at 19:02
1

Turned out to be an issue with Daemon tools for me as well associating the .mdf file as an image.

Randy Thornhill
  • 21
  • 1
1

If you want to copy your .mdf and .ldf file from your database, you can set your database file to 'offline' first . Please follow this steps:

Open your SQL Server 2008(suppose you are using SQL Server 2008 ).
Select your Database that you want to copy then left click.
Go to Tasks -> Take Offline.
Right click.

Then you can copy this database file to any folders.

Tomy Thoan
  • 11
  • 1