Database is not accessible after 'taking it offline' process failed

Question

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

Answer 1

Try following steps.

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

3. Run following command in query analyzer

     ALTER DATABASE `YOURDATABASE_NAME`  
     SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
   

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

1. Set the database mode to multi user again

   ALTER DATABASE `YOURDATABASE_NAME` SET MULTI_USER 
   

Hope this helps.

Answer 2

As an alternative to step one in Furqan's answer, you might not need to restart the SQL Server instance, but only the SQL Server Management Studio instance, which was used to initiate the "Take Offline" task.