| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-05-10 : 01:28:02
|
| If I got a TORN PAGE error, or something like that, in normal operation could something automated mark the database as Read-Only, or somesuch, so that all further updates to the database were disabled until we had a chance to look at the problem?Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-10 : 02:33:57
|
How about this in a job for a suspect database:IF SELECT DATABASEPROPERTY('YourDatabase', 'IsSuspect') = 1 ALTER DATABASE YourDatabase...<add your options here>You could also check the error level of DBCC CHECKDB to decide what to do as well. Tara Kizeraka tduggan |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 02:52:27
|
| What I was after was trapping the very first read error. But you are right, my daily CHECKDB job ought to mark the DB as Read-Only if it spots trouble.Can I trap this somehow?2006-05-08 00:05:33.29 spid56 Error: 823, Severity: 24, State: 22006-05-08 00:05:33.29 spid56 I/O error (torn page) detected during read at offset 0x000000fcc20000 in file 'F:\MSSQL\DATA\MyDatabase.mdf'..Perhaps put a Response on an Alert for that error?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-10 : 10:48:50
|
| Won't @@ERROR bet set to 1 if it fails? Can't you just check for that?Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 14:26:18
|
| "Can't you just check for that?"Yes, and indeed we do, but we just "bail out" if we get that sort of error.But this particular error causes major headaches because the database was corrupted (drive failed in the RAID and, probably long odds but!, consequently corrupted some disk writes [to the other drives] that were happening at the time the disk failed.So if we ever got a "torn page" error again, under any circumstances, I'd be happy that something "caught" the error and set the DB to Read-Only - to prevent any collateral damage.Does that make sense?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-10 : 14:28:59
|
| [code]--your DBCC CHECKDB code or whatever goes hereIF @@ERROR <> 0 OR SELECT DATABASEPROPERTY('YourDatabase', 'IsSuspect') = 1 ALTER DATABASE YourDatabase...<add your options here>[/code]Tara Kizeraka tduggan |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-05-10 : 14:31:01
|
| The only way to do it without constantly running CHECKDBs is to have a job that scans the errorlog for IO errors found during regular query processing.Paul 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-05-11 : 02:56:39
|
| How embarrassing! That is so blinding obvious that its completely beyond my tiny little brain! Thanks Paul.I'll also implement cycling the SQL Errorlog each day to stop the processing load becoming too severeKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-11 : 03:55:35
|
| just an idea, since the errorlog is written only when an error is triggered, does it mean that information is in one of the system tables? like alert messages which you pointed out initially?--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-11 : 04:59:17
|
I was actually thinking I could use a command line tool like FIND to check the SQL Errorlog and then trigger an OSQL command [to mark the DB as Read-Only] if a hit was found, or something more drastic.That would work even if SQL Server was getting very hosed - e.g. I could just stop the SQL Service  Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-05-11 : 20:21:24
|
quote:
Originally posted by jen
just an idea, since the errorlog is written only when an error is triggered, does it mean that information is in one of the system tables? like alert messages which you pointed out initially?--------------------keeping it simple...
Not in SQL Server 2000. In SQL Server 2005 there is the suspect_pages table in msdb that you can query. A row will be added whenever a page IO error is detected.Paul 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-05-12 : 01:43:49
|
| "In SQL Server 2005 there is the suspect_pages table in msdb that you can query. A row will be added whenever a page IO error is detected."Excellent - I like the sound of that. Thanks Paul.Kristen |
|
|
|
|
Can I disable a database on error?