October 26, 2010 at 11:55 am
We had a situation occur last night with our production database server and main database. I'm posting this partly to share what we learned but also to look for any recommendations on how to trace back the log file growth that started the problem in the first place and put processes in place to prevent this from occurring again.
Our production server is relatively small, around 22GB. In the past few days, the log file has grown to 42GB, but shows 98% free space. Due to an IP address change on the server, a server reboot was requested by our Network Group. Since the database is a mirrored server, we paused the mirroring sessions on the databases on that server to prevent a failover situation.
After the reboot of both the principal and partner servers, the main production database remained in (In Recovery) state and the SQL Server log showed errors with this message:
"Bypassing recovery for database 'database_name' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change."
I searched the internet for this error and found the following fix page from Microsoft:
http://support.microsoft.com/kb/979042
Basically the combination of a mirrored database, with a large log file, and a server restart is the combination that created this "in recovery state." The workaround is explained on the microsoft support at the link above, and basically requires a forced drop of mirroring. Once the following script was run, the database recovered immediately:
ALTER DATABASE <Database Name> SET PARTNER OFF
Due to this issue, I now have some questions that I'm hoping someone will address. First, is there any log or report I can check to try to determine what caused the rapid growth of the log file, and also why do log files grow so large yet have so much free space? Is the only way to reduce their size the shrinkfile process? We do take regular and frequent log backups. I've read a lot about shrinking not being a best practice due to database and index fragmentation, so we have been reluctant to shrink files as a routine practice. In this case, I believe we will need to go ahead and shink the file just to keep the database size manageable since we take backups and do restores across servers for disaster recovery and production support purposes.
Thanks for any advice you can provide!
Kwilt
October 26, 2010 at 1:18 pm
could have been a delay in replicating DNS data. it takes us 20-30 minutes to replicate DNS data.
you change the IP, but SQL uses host names to find servers. DNS had the old IP until active directory or DNS replication occurred to update the data on all DNS servers
October 26, 2010 at 1:29 pm
You should be able to find who/what caused your dblog to grow if you have your default trace active.
ref: "Did we have recent autogrow?" by Tibor Karaszi
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
If that doesn't get you information to start with, you can always try products e.g. log explorer
Also, especially with regards to db mirroring, always post your @@version information so everyone knows you are running a >RTM version of SQL2005.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply