CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Vaibhav
Vaibhav

Reputation: 7008

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

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

Upvotes: 12

Views: 23710

Answers (2)

Magnus Kragelund
Magnus Kragelund

Reputation: 370

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.

Upvotes: 12

Furqan Hameedi
Furqan Hameedi

Reputation: 4400

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.

Upvotes: 14

Related Questions