Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Database in Recovery mode Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 2:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 54, Visits: 186
HI,

My developer executed a stored proc and its been almost 24 hrs but its still spinning, so i restarted the MSSQL Server service and after restart my database on which stored proc is running went inot recovery mode. its been 8 hrs and no change. Please provide me the solution, how to bring back my database online and unfortunately we dont take any db backups...

Thanks
Praneeth
Post #1532531
Posted Monday, January 20, 2014 2:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 476, Visits: 1,735
praneethydba (1/20/2014)
HI,

unfortunately we dont take any db backups...


Why? How did you expect to be able to recover the database if it went wrong?
Post #1532535
Posted Monday, January 20, 2014 2:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 54, Visits: 186
HI,

As the database is very big and no place on drive. we stopped taking backup's.
Can you please provide me the solution..

Thanks
Praneeth
Post #1532539
Posted Monday, January 20, 2014 2:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 42,753, Visits: 35,839
Wait.

There is nothing else you can do, particularly since you have no backup. You have to wait until recovery is complete.

When you restarted the server all you did was force SQL to do the rollback of the operation after the restart, this time with the DB unavailable to anyone. It usually takes longer to roll back than the initial operation took, so be patient and don't restart it again, you'll just make it start over.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532541
Posted Monday, January 20, 2014 2:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 42,753, Visits: 35,839
praneethydba (1/20/2014)
As the database is very big and no place on drive. we stopped taking backup's.


So what do you expect to do when something goes seriously wrong and the DB is lost or damaged? Or is the entire of that very big database easily replaceable without any business impact?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532542
Posted Monday, January 20, 2014 2:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 54, Visits: 186
HI ,

Thanks for the reply..
I totally agree with you. We have to take db backup. there is an Impact on the business as week starts from today. My concern is will it be back online or will be go to suspect/damage of my database.

I will wait for 3 more hours, but if i come online well and good, if not is there any other alternative solution inorder to bring back my database usable i.e. online.

Its confirmed that my database will go turn to suspect mode or damaged.

Thanks
Praneeth
Post #1532545
Posted Monday, January 20, 2014 2:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 42,753, Visits: 35,839
Wait until recovery finishes!

There is no alternate solution. There is no magic 'instantly finish recovery' button. Be patient and wait for the recovery to complete, then your DB will come online. If you restart SQL again all you're going to do is force SQL to start recovery over from the beginning, hence wasting the last 8 or so hours.

Wait. And while you're waiting, sort out your backup strategy.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532549
Posted Monday, January 20, 2014 8:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 21,617, Visits: 15,271
Is your database back online?





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1532687
Posted Monday, January 20, 2014 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 54, Visits: 186
No... Not yet.
Its still recovery. I told the scenario to my boss. Just we are all waiting for the database to be online.

Thanks
Praneeth
Post #1532700
Posted Monday, January 20, 2014 8:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 476, Visits: 1,735
Hopefully your boss will see the urgency of taking backups now!
Post #1532703
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse