Database in Recovery mode

Question

Post reply

Database in Recovery mode

praneethydba

SSC-Addicted

Points: 498
More actions
January 20, 2014 at 2:18 am

#287390

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

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

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 SSC-Addicted Points: 498 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC-Addicted Points: 498 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 SSC Guru Points: 281334 More actions · Answer 7

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[/url]
Learn Extended Events

praneethydba SSC-Addicted Points: 498 More actions · Answer 8

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 SSC-Dedicated Points: 32407 More actions · Answer 9

Hopefully your boss will see the urgency of taking backups now!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

While waiting for it to come online, have you checked the disk system? Have you checked general server health? Have you checked permissions (make sure they haven't changed)?

Your recovery process could take a very long time based on the info provided. While that is happening, you might as well do everything possible to ensure there is nothing else causing delays or problems.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

david.john.gilligan Default port Points: 1471 More actions · Answer 11

praneethydba (1/20/2014)
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

The best backups are not stored on the same drive, IMHO. And with the cost of space these days........

praneethydba SSC-Addicted Points: 498 More actions · Answer 12

HI,

Still in Recovery. I am Panic.

My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.

So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.

Can you please let me know the link or site

Any additional tips are highly appreciated!!!

Thanks

Praneeth

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

praneethydba (1/21/2014)
Still in Recovery. I am Panic.

Be patient.

The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side

My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.

Sounds like you need to get someone in to evaluate and make recommendations. If it's that much of a mess, it's going to be a huge job just figuring out where to start.

I studied on net to improve performance we can do table partition for fast retrieval of data.

Um, no.

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 14

praneethydba (1/21/2014)
HI,
Still in Recovery. I am Panic.
My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.
So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.
Can you please let me know the link or site
Any additional tips are highly appreciated!!!
Thanks
Praneeth

Partitioning is not really a performance tuning mechanism. It's primarily a data management mechanism that can have performance improvements. I agree with Gail, get a professional in to do an assessment. Barring that, get a copy of my book. At least you'll have a functional starting point for figuring this stuff out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning