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 was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying

'an exception occured while executing a transact SQL statement or batch 

-> ALTER DATABASE failed because a lock could not be placed on database 'myDB'. Try again later.
    ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)'

Now if I try and access the DB it says it is not accessible. What can I do to make my DB accessible again?

My aim was to detach the DB, relocate its secondary database file to a new drive and reattach it (simply because of space issues).

share|improve this question
    
What si the current state of the database? select name, state from sys.databases –  Remus Rusanu Jul 9 '12 at 7:19
    
Try to refresh the "databases" node in Management Studio. Perhaps the database is already offline. –  Andomar Jul 9 '12 at 7:21

3 Answers 3

up vote 1 down vote accepted

You should never do this using detach. Until we know more about exactly what state your database is in now, for next time, you should do the following:

  1. ALTER DATABASE SET OFFLINE
  2. For each file, ALTER DATABASE MODIFY FILE
  3. copy the files to the new location
  4. ALTER DATABASE SET ONLINE
  5. when everything is working, delete the original files

The reason you don't want to do this with detach, and the reason you want to copy the files instead of moving them, is because when something goes wrong in either of those processes, you have exactly 0 copies of your database. Or the situation you're in now.

share|improve this answer

It sounds like your database is already offline. If you can move your secondary files around like you want, it should be fine.

share|improve this answer

I tried the following steps.

Restart the SQL server service using services.msc console. Now connect to your server using SQL Server Management Studio.

Run following command in query analyzer

  ALTER DATABASE `YOURDATABASE_NAME`  
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

Now Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears

OR 5. Run your command to Relocate the secondary database.

Set the database mode to multi user again

ALTER DATABASE `YOURDATABASE_NAME` SET MULTI_USER 
share|improve this answer
    
Did this fix the problem? If not, please delete this answer and add this information to the question. –  Aaron Bertrand Jul 9 '12 at 12:12
    
@AaronBertrand OP cross posted here and copied/pasted answer –  JNK Jul 9 '12 at 17:15
    
I posted this question in stackoverflow as well. Got the answer there. Tried and it executed fine. To help others confronting same problem, I have posted the answer here too. I wonder why M I downvoted? –  vaibhav Jul 10 '12 at 12:54
    
+1 It actually helped me, the solution was to restart sql server, regarding your downvote it appears that there are many idiots out there even more than stackoverflow, I have also recently been downvoted here by people claiming to know better than Microsoft without providing any proof or even any link that backs it. –  yoel halb Sep 12 '12 at 22:32

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.