CODEWITHSUNDEEP
sql-server-expressssms
Morten Holmgaard
Morten Holmgaard

Reputation: 7806

SQL Server Express unable to attach or delete mdf file

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..

Upvotes: 1

Views: 16103

Answers (3)

Tomy Thoan
Tomy Thoan

Reputation: 11

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.

Upvotes: 1

Randy Thornhill
Randy Thornhill

Reputation: 21

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

Upvotes: 1

Krzysztof Kozielczyk
Krzysztof Kozielczyk

Reputation: 5937

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.

Upvotes: 4

Related Questions