Database recovery

Question

Post reply

Database recovery

Tara-1044200

SSCoach

Points: 15951
More actions
April 27, 2009 at 8:34 am

#155613

After the reboot my database has gone into recovery mode and its been few hours still not coming up. How do i get the database back online, i am not worried about the pending transactions.
looks like the log has grown too much, is there a way i can delete log file and bring back the database.
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Here is my cent. Check your error log. It may be the roll back/forward. If so, you may not have other choice.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I dont see any error message or roll back information except that

Recovery of database (11) is 0% complete (approximately 79437 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 3

Database is still recovering, nothing much you can do, you have to wait.

Did you restart the server when any big transaction or process is happening?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

yes there was some processing running and the reboot was done according to our shedule.

anyways if nothing much i can do i will wait to recover.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

Tara (4/27/2009)
yes there was some processing running and the reboot was done according to our shedule.
anyways if nothing much i can do i will wait to recover.

Was it a clean shut down? There could be some uncommited transactions that SQL server is trying to roll back may be. How log has it been like that for? Leave it as it is and check out your SQL error log for any potential reasons.

ichbinraj SSCommitted Points: 1627 More actions · Answer 6

Is it possible to restore from recent backup ?

look like its going to take nearly 22 Hrs to complete.

experts : can we do this thing ?

Thanks

Ichbinraj

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

ichbinraj (4/27/2009)
Is it possible to restore from recent backup ?
look like its going to take nearly 22 Hrs to complete.
experts : can we do this thing ?
Thanks
Ichbinraj

Yes, you can restore it using the clause WITH RECOVERY. but why?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Tara (4/27/2009)
yes there was some processing running and the reboot was done according to our shedule.

...why I'm having troubles to digest this statement?

Your options are :

1) Sit tight and wait until it completes.

2) Restore/recover your database -I wouldn't do this.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 9

Tara (4/27/2009)
yes there was some processing running and the reboot was done according to our shedule.
anyways if nothing much i can do i will wait to recover.

Tara,

Going forward, if this is not an automated scheduled reboot but manual schedule reboot, before reboot check whether any processes is running or not, if any, wait till the process gets completed and then go a head

If you have scheduled reboot (automatic) , schedule in such a way that no processes runs i.e inconsultation with application owners.

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 10

If you really do need to reboot the server I'd recommend running something to stop database activity immediately prior to the reboot.

If I want to kick all users out of a database immediately, killing any in-flight transactions, I use the following statement:

alter database MyDatabase set restricted_user with rollback immediate

If you want to give in-flight transactions a chance to complete then instead of using "immediate" you can use "after nn [seconds]".

(if your application is one of those seemingly myriad third-party apps that "requires" db_owner or sysadmin rights then use "single_user" instead)

After the server has come back up you will need to run the following command:

alter database MyDatabase set multi_user

This could be set up as a job to run on service start.

Note that this is not perfect. If there are large in-flight transactions you still need to wait for them to be rolled back, which can take a long time. It sounds like you need to review the operation of the system prior to the reboot to work out what large transactions could have been running. It's going to give a much better result if you can manage the scheduling/execution of these so they aren't running when the reboot is scheduled to happen.