January 14, 2009 at 7:32 am
Hi ,
We have a pair of mirror servers. Windows server OS rebooted unexpectedly on one of the servers and databases on second ( principal ) server went in Recovery mode ( instead of Principal, diconnected ). Is there any way to make databases in principal server usable again? everytime i try to detach or even delele database , it says database in In recovery and hence , operations can not be performed.
Vibz
January 15, 2009 at 4:26 am
The simple answer to your questions is to run:
RESTORE DATABASE db_name WITH RECOVERY
However, it is likely that you have other problems occurring here (if the mirroring worked correctly, then the Mirror DB should have come online). Although, that depends if your mirror was configured as High Safety...? With/without automatic failover?
Andy
January 15, 2009 at 2:46 pm
Andys right, however this may result in you having to set up mirroring again.
a few q's:
1. whats status is the mirror DB in?
2. also how is your mirroring configured? do you have a witness server?
3. does the mirroring monitor tell you anything useful?
Carlton..
January 15, 2009 at 3:57 pm
Hello,
This is my first post ever on SqlServerCentral so bear with me. We have a high-availability mirror set up in our office running on Windows 2003 srvr 64bit. I had my prinicipal lock up the same as yours and recovered using the script mentioned in the earlier post. However, after working extensively with MS SQL Server Support, they let me know of a little problem with mirroring in SQL Server 2005. When the transaction log got big (and the actual number is not known but may be relative to other factors), the principal can be thrown into recovery mode. Once I shrunk the logs down, I was good. We now monitor it closely to make sure it doesn't happen again.
Just food for thought. I don't know if it applies to your situation but it's what we found out and what worked for us.
Phillip
January 16, 2009 at 1:46 am
Phillip Oyog (1/15/2009)
Hello,This is my first post ever on SqlServerCentral so bear with me. We have a high-availability mirror set up in our office running on Windows 2003 srvr 64bit. I had my prinicipal lock up the same as yours and recovered using the script mentioned in the earlier post. However, after working extensively with MS SQL Server Support, they let me know of a little problem with mirroring in SQL Server 2005. When the transaction log got big (and the actual number is not known but may be relative to other factors), the principal can be thrown into recovery mode. Once I shrunk the logs down, I was good. We now monitor it closely to make sure it doesn't happen again.
Just food for thought. I don't know if it applies to your situation but it's what we found out and what worked for us.
Phillip
Thank you for the feedback.
Can you provide @@version info applicable with your case.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2009 at 2:42 am
Thanks Philip, interesting.
Do you know if the problem lies purely with the size of the LDF file (and how full it is), or is it related to the speed the LDF grows at? In other words, do problems occur when the number of changes per second is too great?
Also, do you suffer the same problems without a Witness Server (no automatic failover?
Andy
January 16, 2009 at 4:18 am
Phillip Oyog (1/15/2009)
Hello,This is my first post ever on SqlServerCentral so bear with me. We have a high-availability mirror set up in our office running on Windows 2003 srvr 64bit. I had my prinicipal lock up the same as yours and recovered using the script mentioned in the earlier post. However, after working extensively with MS SQL Server Support, they let me know of a little problem with mirroring in SQL Server 2005. When the transaction log got big (and the actual number is not known but may be relative to other factors), the principal can be thrown into recovery mode. Once I shrunk the logs down, I was good. We now monitor it closely to make sure it doesn't happen again.
Just food for thought. I don't know if it applies to your situation but it's what we found out and what worked for us.
Phillip
Thats really useful Philip. Thank you 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 16, 2009 at 4:19 am
Phillip Oyog (1/15/2009)
Hello,This is my first post ever on SqlServerCentral so bear with me. We have a high-availability mirror set up in our office running on Windows 2003 srvr 64bit. I had my prinicipal lock up the same as yours and recovered using the script mentioned in the earlier post. However, after working extensively with MS SQL Server Support, they let me know of a little problem with mirroring in SQL Server 2005. When the transaction log got big (and the actual number is not known but may be relative to other factors), the principal can be thrown into recovery mode. Once I shrunk the logs down, I was good. We now monitor it closely to make sure it doesn't happen again.
Just food for thought. I don't know if it applies to your situation but it's what we found out and what worked for us.
Phillip
Thats really useful Philip. Thank you 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 20, 2009 at 10:27 am
Hello,
Sure. It's Microsoft SQL Server 2005 - 9.00.3073.00 (X64) Aug 5 2008 14:31:47 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks,
Phillip
January 20, 2009 at 10:39 am
Hi Andy,
It seems to be only the size of the file. The number of transactions don't seem to cause any issues. We are planning to do some extensive operational recovery and dissaster planning testing in the next few months. During the tests, we will then recreate the scenario to see if we can determine the threshold for our environment. Since we fixed it a few months ago, we've followed MS reccommendation of keeping the log size optimized and down to avoid the situation entirely.
The database we have supports an outside product that wasn't really designed for scope of deployment our customers decdided upon. It was also never tested for a mirror. So...we're kinda the guinea pigs.
January 20, 2009 at 10:39 am
Is this related to bug http://support.microsoft.com/kb/959006/
SP3 has it in its fix list :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 20, 2009 at 11:03 am
Hmm...doesn't look the same as what we encountered. But is very interesting. I'll look into this more. Thanks!:)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply