How to remove stuck mode in SQL Server?

Question

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?

Answer 1

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'

Answer 2

Please, don't ever kill the sqlserver.exe task in Task Manager - what you're doing is equivalent to pulling the power plug on the server in mid-stride.

"In recovery" means that SQL Server is trying to go through and repair ("recover") any work that needs to be rolled back in the database as a result of this uncontrolled interruption, which could be a considerable amount since your query took so long to complete. Often, rolling back a query can take longer than it actually took to execute. I would advise you just to wait it out and never do it again. ;)

Next time a query runs away, there are a few options:

If the query is in your Management Studio, just click the "Cancel Executing Query" button (Alt+Break)

If the query is running in the background (for instance, in a service), or it's not your query, you can find it using sp_who2:

EXECUTE sp_who2;

Look for a process where the "command" is something like "SELECT", "EXECUTE", "INSERT", etc, and where you have a high number in the CPUTime or DiskIO column. The SPID number for that process can be used with the KILL command to terminate that specific process:

KILL 123 -- where 123 is your offending SPID.

The KILL command will stop one specific query and roll it back in a controlled manner without putting your database in a recovery state.