Sql-server – Database is not accessible after ‘taking it offline’ process failed

sql serversql-server-2008-r2

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

Best Answer

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:

ALTER DATABASE SET OFFLINE
For each file, ALTER DATABASE MODIFY FILE
copy the files to the new location
ALTER DATABASE SET ONLINE
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.

Related Solutions

Sql-server – Indexes file damaged, was on ramdisk

Once the database is detached you will probably not be able to attach the data files. Since the file operation that occurred is an almost guaranteed data corruption I think your best option here is to perform a restore from a native SQL server backup or export of some kind, or recover any snapshots you might have taken of the environment.

Sql-server – restore could not start database

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):
```
RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
```
In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

Best Answer

Related Solutions

Sql-server – Indexes file damaged, was on ramdisk

Sql-server – restore could not start database

Related Question