Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lincolnburrows
Yak Posting Veteran
52 Posts |
 Posted - 2014-08-04 : 07:06:06
|
| 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
38200 Posts |
Posted - 2014-08-04 : 12:23:41
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
camero
Starting Member
1 Post |
Posted - 2014-08-05 : 04:31:59
|
| 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. |
|
|
|
Lincolnburrows
Yak Posting Veteran
52 Posts |
Posted - 2014-09-04 : 06:29:14
|
Thanks Camero for your help.  |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
How can I recover SQL database from stuck mode?