Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 22 '12 at 12:16

This question came from our site for professional and enthusiast programmers.

    
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 '12 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];
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.

share|improve this answer
    
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. –  praseodeveloper May 23 '12 at 6:20
    
Huh? You can detach the database but you can't alter it before doing so? Sorry but that makes ZERO sense. –  Aaron Bertrand May 23 '12 at 11:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.