Sql-server – How to remove stuck mode in SQL Server

sql server

I was clearing tables by using query in SQL Server. A query that normally takes 2 minutes took the better part of 3 hours… and it wasn’t even close to being complete. Therefore, I killed SQL Server in task manager because I felt like I had no other choice.

Upon logging back in, the database has been (in Recovery). According to various posts I read online, simply detaching it and reattaching it should work fine however, when I try to run queries to detach it I get the following error:

Alter database failed because a lock couldn’t be obtained for database <sel>

I also read that this was due to a connection not being terminated but everything I tried gives that same lock error. I made sure I was in master, I reset the PC, un-plugged network table, tried the commands in the GUI. Nothing is letting me detach said database or remove it from In recovery.

What can I do?

Best Answer

You did blunder by restarting SQL Server. You should have not done that.Unless SQL server will recover the database fully and comes to state where database finds itself in same consistent state as it was before the force shutdown it will show in recovery state. Your best bet is to wait and watch you cannot run any transaction on recovering database. Also since you manually killed SQL server process while it was rolling back a query its even more worse. Now when SQL server database will come online the whole rollback process will start from beginning not from point where you killed SQL server process/query. So it might take time and unless it fully rolls back the previous killed query it won't come online neither you can force it to.

One advantage you can have is if you have Enterprise edition SQL Server will come online after redo phase of recovery. So that might be little relief to you.

PS: Seriously avoid any such random suggesstion given on web. You should take this as a lesson to never kill SQL server process or restart SQL Server if certain query is taking time. You should wait and chec rollback status of query using below script

select session_id,
blocking_session_id,
wait_type,
percent_complete, 
((estimated_completion_time)/1000) as Estimated_Complettion_time_Sec,
((total_elapsed_time)/1000) Toatal_Elapsed_time_Sec 
from sys.dm_exec_requests where status='recovery'

Related Solutions

SQL Server Transaction Log – Rebuilding the Transaction Log in SQL Server 2005

Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick:

ALTER DATABASE recovery_test_2 SET EMERGENCY;   
ALTER DATABASE recovery_test_2 SET SINGLE_USER;  

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) 
WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation:

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

You asked about the difference between:

DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS) and
ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

The thing is that you can run both to rebuild the log file, but with CHECKDB you rebuild the log and check database for integrity errors as well.

Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.
ALTER DATABASE REBUILD LOG ON... is an undocumented procedure and requires a subsequent DBCC CHECKDB to fix any errors.

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

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.

Best Answer

Related Solutions

SQL Server Transaction Log – Rebuilding the Transaction Log in SQL Server 2005

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

Related Question