Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RayG
Starting Member
2 Posts |
 Posted - 2006-06-02 : 01:33:19
|
| We've had some problems with one of our SQL Servers past couple of days. Running SQL 2000 sp 3aWe got one database marked as suspect (not due to low disk space IMO) but to some other I-O problem. At first I thought we might have problems with the disk array but after running the diagnostics and chkdsk it is clean. When server comes up the same folks run the same reports and processes and complain about time outs/long running queries. I suspected the Tempdb and found that it had no start size specified so it had to grow itself from a meg to 600+ Megs in a hurry after a server re-start. Today we saw I-O errors in the log affecting TempdBAnyway we moved TempDB to the disk array where it has more room to grow.2006-05-31 14:46:55.93 spid18 Recovery of database 'CDS' (14) is 0% complete (approximately 3 more seconds) (Phase 2 of 3).2006-05-31 14:46:56.53 spid18 Error: 823, Severity: 24, State: 22006-05-31 14:46:56.53 spid18 I/O error (torn page) detected during read at offset 0x00000011fd6000 in file 'd:\mssql\data\CDS_Data.MDF'..2006-05-31 14:46:56.62 spid18 Error: 3313, Severity: 21, State: 22006-05-31 14:46:56.62 spid18 Error while redoing logged operation in database 'CDS'. Error at log record ID (43967:22105:3)..2006-05-31 14:46:56.78 spid18 Error: 3414, Severity: 21, State: 12006-05-31 14:46:56.78 spid18 Database 'CDS' (database ID 14) could not recover. Contact Technical Support..2006-05-31 14:49:37.03 spid1 Recovery complete. Q1. Anybody seen similar behaviour? Can problems growing Tempdb affect other DBs?Q2. Is this Hardware-related? Are you 100% sure?Q3 There appeared to be a Hotfix related to 3313 and I would think it would have made it into 2000 SP4. Would it address this particular issue? Thanks for your help...Ray |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-02 : 02:03:38
|
| Q1. Anybody seen similar behaviour? Can problems growing Tempdb affect other DBs?No.Q2. Is this Hardware-related? Are you 100% sure?Most likely. You've got a torn page. That's when the page leaves SQL Server ok but is only partially written to disk. When the page is read back in, we can tell that only part of the page was written out correctly. Usually this happens after a power failure affecting a drive with write-caching enabled but it could just be dodgy hardware. Did you see these errors after restarting SQL Server to move TEMPDB? How did you restart SQL Server? Gracefully or by bouncing the box's power?Q3 There appeared to be a Hotfix related to 3313 and I would think it would have made it into 2000 SP4. Would it address this particular issue? No. The hotfix description (http://ask.support.microsoft.com/kb/891585) clearly states that you will see a ceratin assert in the error log. In this case, the 3313 is caused by the torn page that recovery encountered. 3313 is a generic message meaning that a redo of a log record failed. The actual cause is usually given in a previous message (like the 823 one)What IO errors did you see affecting TEMPDB? Do you have a valid set of backups to restore from?(BTW, checked your TZ and you're 3 hours ahead of me here in WA - I won't see any replies until 12-1pm EST. If this is urgent and you don't know what to do, you should contact Product Support who will be able to help you get back up and running).ThanksThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-02 : 06:39:34
|
| Don't know if its relevant to this case, but we've had Torn page errors in the past when a single drive in a RAID 5 failed. So much for "Redundant ..." !!Might be worth just checking that you haven't had a drive failure - if so you may be running one drive short just now!!Kristen |
|
|
|
|
|
|
|
|
Database Corruptions, Tempdb, Error: 3313