Failure to detach database

Question

I used sp_detach_db to detach SQL Server 2005 databases. This works properly in Windows XP and Windows 7.

However, the same fails in Windows Server 2008. The error message states:

The process cannot access the file SimpleProject_device.ldf as it is being used by another process

But, I am able to delete both .mdf and .ldf files manually inspite of getting this error. I guess, if the database was still attached, delete operation should not have been allowed by the OS.

Edit: The problem was that the Connection object had been disposed before detach. (God knows how). Therefore, I have added a condition to ensure that the connection exists before trying to detach the database.

Currently, I am getting another error.

An error occurred during database detach:  "A transport-level error has
occurred when sending the request to the server. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the
pipe.)",30791590,8688858,5/22/2012,9:37:05 PM

Does anyone know why I'm getting this error?

Answer 1

You or some other user is probably still connected to the database (it could be your current query window, or Management Studio's Object Explorer). To break this you can set the database to single user mode, which rolls back any work they were doing (which shouldn't matter, since you're detaching the database anyway).

USE [master];
GO
ALTER DATABASE SimpleProject_Device SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you should be able to detach.

However if you can delete the file then my guess is you are talking about two different physical files. Are you using SQL Server Express and the User Instances / attachDbFileName features perhaps? If so you can search the system for files named Simple_Project_device.ldf - I bet there are more than one, and the one you're trying to detach is not the same one you're trying to delete.

In any event, detach is not the right way to move a database: BACKUP / RESTORE is better. Why? Because if something goes wrong with the backup or the restore, you still have the original. If something goes wrong during detach, you now have ZERO usable copies of your database. With backup/restore, after the restore is successful, you can just drop the source database.