PRINCIPAL SERVER goes in "In Recovery"

Question

Post reply

PRINCIPAL SERVER goes in "In Recovery"

Joy Smith San

SSC-Insane

Points: 24877
More actions
October 21, 2011 at 1:37 am

#250576

Hi All,
A small database is mirroed.
Issue is PRINCIPAL SERVER goes in "In Recovery" when we did a manual failover.?
Waited for long time but still shows "In Recovery" only.
What could be the causes and how to check.? What are the solutions. ?
Thanks in advance.
Smith

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Lots of VLFs

Large tranactions to roll back.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

GilaMonster (10/21/2011)
Lots of VLFs
Large tranactions to roll back.

Thanks Gila...

What abt solution .??

Thanks again.

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

Google down?

For lots of transactions, wait. There's nothing else that can be done there (especially if the failover was uncontrolled)

For VLFs - http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Also, what's the sizes of the mirroring redo queue?

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thank you Gila.

Had tried in google..

Got some posts.. but was nothing specific.. most of them were about the status "Receovering" of mirror.. nothing was abt to recovery..

In our case I suspect VLFs.. as there was no any long running open transactions as such I believe...

Thanks.

Hugo Mendes Valued Member Points: 74 More actions · Answer 5

Before failing over can you confirm that you see the following in Management Studio:

Principal Server: DatabaseName (Principal, Synchronizing)

Mirror Server: DatabaseName (Mirror, Synchronized / Restoring...)

If the above is not the case, then it would suggest Mirroring is not setup correctly before attempting the failover.

Then when you fail over:

1. Connect to Principal SQL Server

2. Right click principle DB.

3. Properties, Mirroring - Failover

After refreshing the databases on both servers you should see the mirroring descriptions reversed.

Is this the point where you see one of the DB's in restoring mode?

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 6

Is this the point where you see one of the DB's in restoring mode?

It was not "restoring"... It was in "RECOVERY"...

Mirroring was setup properly... I failover it using scripts.. and same used to work fine..

Probelm was with PRINCIPAL.. Not Mirror DB..

Thanks.

Smith.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

For lots of transactions, wait. There's nothing else that can be done there (especially if the failover was uncontrolled)

We waited for almost 5 hours, but no luck. Still in Recovery. Fortunately We have a valid recent backup.

What would you suggest in this case ? How can we cancel and restore this full backup ?

For VLFs - http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Yes, went through the link (had some idea before..). But nowhere it's mentioned how to go about when there's some issue like this.

Also, what's the sizes of the mirroring redo queue?

Shows 0 kb.

Thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Joy Smith San (10/22/2011)
For lots of transactions, wait. There's nothing else that can be done there (especially if the failover was uncontrolled)
We waited for almost 5 hours, but no luck. Still in Recovery. Fortunately We have a valid recent backup.
What would you suggest in this case ? How can we cancel and restore this full backup ?

How long does a backup take to restore? Are you sure it'll be faster?

How long does the error log log say the recovery will till take?

How much data will you lose by restoring the earlier backup? Is it an acceptable data loss?

For VLFs - http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Yes, went through the link (had some idea before..). But nowhere it's mentioned how to go about when there's some issue like this.

Really? Did you read the whole thing? Point 8, after the initial description of the problem states:

To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

And the the article lists 3 steps to fix it.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 9

Thanks Gila.

Yes, restore should be faster. Data loss shouldn't be there I believe. We had taken backup just before we did fail over.

Sorry, I will again go through the link. May be dint read properly..

Thanks again.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

Did you check the error log to see how long recovery is still expected to take?

Bear in mind, if you restore you'll also have to reconfigure the mirroring.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 11

GilaMonster (10/23/2011)
Did you check the error log to see how long recovery is still expected to take?
Bear in mind, if you restore you'll also have to reconfigure the mirroring.

Might take more than 2 hours now. Client says they done want to wait any longer.

Reconfiguration is OK with them. Shall I try to detach the DB .?

Thanks.

Smith.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

It's unlikely to detach. Probably going to require something like stopping SQL, moving the files and then restarting SQL so that the DB comes up recovery pending and then restoring over the top.

But it's entirely up to you, this is not something I would endorse doing.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 13

Joy Smith San

SSC-Insane

Points: 24877

October 24, 2011 at 2:59 am

#1398550

Thanks alot Gila.

Will try and get back.

Smith.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 14

Really? Did you read the whole thing? Point 8, after the initial description of the problem states:

To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

And the the article lists 3 steps to fix it.

Correct Gila.. But those steps can be carried out only when DB comes out of "In Recovery" state, right ?

Anyway, We managed to restore the backups.. Keeping an eye on it now. Will recheck Log, VLF etc.

Thanks.