SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database in Recovery mode


Database in Recovery mode

Author
Message
praneethydba
praneethydba
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 188
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
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7036 Visits: 4849
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?
praneethydba
praneethydba
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 188
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229976 Visits: 46344
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229976 Visits: 46344
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, MVP, M.Sc (Comp Sci)
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


praneethydba
praneethydba
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 188
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229976 Visits: 46344
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, MVP, M.Sc (Comp Sci)
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


SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67981 Visits: 18570
Is your database back online?



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

praneethydba
praneethydba
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 188
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
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7036 Visits: 4849
Hopefully your boss will see the urgency of taking backups now!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search