SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How can I recover SQL database from stuck mode?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lincolnburrows
Starting Member

42 Posts

Posted - 08/04/2014 :  07:06:06  Show Profile  Reply with Quote
Hello. Actually I was clearing tables by using query in SQL database. 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 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 post 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 <selcon>
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-plug network table, tried the commands in the GUI. Nothing's letting me detach said database or remove it from In recovery.
Any help would be appreciated. Thank you

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/04/2014 :  12:23:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to stop doing all of those steps and let the database recover. You canceled a large transaction, and it needs to rollback. You can check the Error Log for its progress as SQL will report the percentage of the 3 phases.

I don't understand the posts saying to detach/attach. That doesn't make sense.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

camero
Starting Member

1 Posts

Posted - 08/05/2014 :  04:31:59  Show Profile  Reply with Quote
If you notice in MS SQL Management Studio that a database is stuck in restoring mode, open a new query by clicking on the 'new query' button and enter the following command:


RESTORE DATABASE [databasenamehere] WITH RECOVERY;


Click on 'Execute'. When you refresh MS SQL Management Studio, the database will be in a normal operational state.
Go to Top of Page

Lincolnburrows
Starting Member

42 Posts

Posted - 09/04/2014 :  06:29:14  Show Profile  Reply with Quote
Thanks Camero for your help.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 09/05/2014 :  01:49:45  Show Profile  Visit jackv's Homepage  Reply with Quote
Normally , you'd kill the transaction as opposed to closing down SQL Server - to monitor transaction rollback - utilise this query - http://www.sqlserver-dba.com/2011/11/monitoring-a-rollback-and-sysdm_exec_requests.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000