December 3, 2008 at 2:17 pm
I just recovereed the huge DB after 4hrs and after that we reboot the server but after rebooting again that DB went into ( In Recovery) mode, why is that? do i need to wait another 4hrs?
December 3, 2008 at 2:22 pm
Please post the latest SQL error log. The one after the latest reboot.
Have you checked the system event log for hardware-related errors?
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
December 3, 2008 at 2:36 pm
every time i restart the server some of the db's are in recovery state. it happened in another server too. what shud i do.
2008-12-03 15:06:48.15 spid41s Recovery of database 'STATE_CA' (23) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2008-12-03 15:06:48.15 spid41s 1 transactions rolled back in database 'STATE_CA' (23). This is an informational message only. No user action is required.
December 3, 2008 at 2:52 pm
Have you checked the system event log for hardware-related errors?
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
December 3, 2008 at 2:58 pm
have you changed the account sql service runs under by any chance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 3, 2008 at 3:05 pm
how do i check that and what do i need look for into it?
December 3, 2008 at 3:12 pm
i take it that means you havent changed the account, could anybody else have access to do this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 3, 2008 at 3:14 pm
Is that error log you just posted from the same system that had the 823 errors? It doesn't appear to be so, the database names are different.
Is the system that had the 823 errors also exhibiting the DBs in recovery? If so, please post the latest error log from that server (the one with the database DCC_CA on it), from the last restart of the server up to the latest entry.
Do the servers that have the problems share a common SAN?
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
December 3, 2008 at 3:21 pm
I dont see naym esg from the error logs but if some one shed light on my doubt tht wud be gr8.
In general what wud be the reason behind the few db's going into recovery state every time after restarting the server though i dont have any pending trasaction to roll back are fwd.
thanks
December 3, 2008 at 3:29 pm
Mike Levan (12/3/2008)
In general what wud be the reason behind the few db's going into recovery state every time after restarting the server though i dont have any pending trasaction to roll back are fwd.
I don't know! That's why I'm asking for the error log.
Does the server that was having 823 errors earlier have the problem with databases in recovery after a restart?
If it does, please find the latest error log, from the last restart up til the latest entry and post 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
December 3, 2008 at 7:04 pm
First off, please post in complete words rather than text-message jargon - it makes posts much easier to read.
Your I/O subsystem has issues - plain and simple. The first error log that you posted with flush problems *screams* the I/O subsystem is not servicing writes correctly. You need to get someone from your hardware vendor, or your hardware administrator, or a production DBA involved with this. From the names of these databases, it looks like a production system from a pharmacuetical company - do you have a DBA? Now this is blunt, and no offense, but from the questions you're asking, you don't sound like a production DBA - and someone with more knowledge should be troubleshooting and rectifying this problem.
At the very least, if you're the only person available to troubleshoot this, you should call Microsoft's Product Support to help you through this as we're not going to be able to help you effectively over such a low-bandwidth, high-latency medium like an Internet forum. You've got more problems with this system than can be dealt with without dedicated help, and I'm concerned that things are going to be made worse by us trying to help without actually interacting with the broken system.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 22, 2011 at 11:02 am
FYI: we experienced a similar problem and I thought I'd share for someone else's future benefit, some of our read-only databases were mapped to an iSCSI drive on another file server. When that file server had its OS patched and along with a required reboot, the database server's iSCSI drive mapping was disconnected and those archived databases lost temporary access to the mdf & ldf files, triggers SQL server to generates I/O errors (among the db symptoms we got are not in Recovery mode, but just no db objects are seen). Despite the fact we can see the mapped iSCSI drive from the db server, we decided to do a network disconnect and reconnect of the iSCSI drive on the database server anyways and do the de-attach and re-attach of those dbs and the problem was solved.
HTH,
Warren
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply