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

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.

Answer 2

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

Answer 3

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