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?

  • Use Process Monitor to find out what process is accessing the files. However, if you can delete it then the file isn't being accessed. Are you sure it isn't just some delay after detach etc?
    – gbn
    May 22, 2012 at 11:54

1 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];

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.

  • Thanks Aaron for your help. However, we are not permitted to modify the database (customer requirement). Also, I shall crosscheck whether I am trying to delete another copy of the database.
    – praseo
    May 23, 2012 at 6:20
  • Huh? You can detach the database but you can't alter it before doing so? Sorry but that makes ZERO sense. May 23, 2012 at 11:10

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.