SQL Server Database Showing in Recovery

 It was 7 O’Clock in the evening and I was about to step out of the office but found one of the colleagues looking disturbed and struggling and on asking came to know that the database of the production application is showing in recovery and recovering since almost 4-5 hours. This happened after some new code deployment in the production server. The best part was that they were having backups till the time of failure and application can recover just by restoring the backups. So we thought of a quick action plan and executed as below.

·         Check what is blocking the database recovery which can be done by inspecting below query.

SELECT DB_NAME(dbid),* FROM dbo.sysprocesses WHERE spid > 50 and spid <> @@SPID

·         Try to kill the blocking SPIDs but got the error message “Only user processes can be killed”.

·         Try to delete the database by putting it to single user mode but the execution was just going on and on inspecting the query in first step found that drop database was waiting on recovery to finish.

·         Try to make the database offline but it was also waiting on recovery to finish.

·         Lastly we decided to

a.    Stop the server because anyway the application was already down.

b.    Then we cut and pasted the ldf, mdf and ndf files to another folder (Actually cut-paste is very fast option as it just changes the pointer in the disk).

c.    Start the SQL Server service again the recovery mode was gone.

·         Delete the database and restored the latest backups.

The query to find the file path of a database is as below.

SELECT D.dbid, D.name, F.physical_name FROM sys.master_files F JOIN dbo.sysdatabases D on F.database_id = D.dbid

This may not be a perfect way to deal with the situation but it worked  very quick and nicely because recovery is done through files and when there are no files no recovery can be done. Moreover we were having liberty to restart the SQL Server service as well which others may not have on these scenarios.