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 |
|
kghammond
Starting Member
4 Posts |
 Posted - 2006-02-27 : 15:47:11
|
| I have written a procedure to detect if there are errors returned via checkdb.DECLARE @msg varchar(60) DECLARE @db varchar(40)CREATE TABLE #Result (Error INT, Level INT, State INT, MessageText VARCHAR(7000),RepairLevel INT,Status INT,DbId INT,Id INT,IndId INT, [File] INT,Page INT,Slot INT,RefFile INT,RefPage INT,RefSlot INT, Allocation INT)SET @db = 'dbname'INSERT INTO #Result EXEC ('dbcc checkdb (' + @db + ') with tableresults')SELECT @msg = MessageText FROM #Result WHERE MessageText Like 'CHECKDB found%'IF PATINDEX('CHECKDB found 0 allocation errors and 0 consistency errors%',@msg) = 0 PRINT 'Error Found -- Do Stuff'DROP TABLE #ResultMy question to some more expeirienced DBA's... After I check for errors, If I find an error how should I proceed?My thought is to generate an email, put the db into single user mode then attempt a repair_fast, and recheck for errors. If errors still occur, attempt a repair_rebuild and check for errors again. If their are still errors send an urgent email alert requiring a restore of the db.Can anyone shed some light on how they are alerted and respond to dbcc checkdb errors? Is the above procedure overkill?Thank you,Kevin |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-27 : 15:53:06
|
quote:
After I check for errors, If I find an error how should I proceed?
It depends on the error. Nothing should be automated though that fixes the corruption. A DBA should work on it and not some automated process. We are alerted of errors via the job notification, MOM alerts, and custom process.Tara Kizeraka tduggan |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-27 : 17:51:04
|
| I've got a question: ... are there errors that could crop up in CHECKDB that this won't catch?Alternatively are there "better" ways of checking that CHECKDB worked OK?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-27 : 18:02:29
|
quote:
Alternatively are there "better" ways of checking that CHECKDB worked OK
The job will fail if DBCC CHECKDB has errors.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-27 : 19:00:01
|
| is there an easy way to test that a routine [to check for CHECKDB errors] works?Do I need to use a Hex Editor to "break" and MDF file?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-27 : 19:06:19
|
quote:
Originally posted by Kristen
is there an easy way to test that a routine [to check for CHECKDB errors] works?Do I need to use a Hex Editor to "break" and MDF file?Kristen
Update the FirstIAM column in sysindexes. But please do this on a system that you can rebuild if things really go wrong.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-27 : 20:53:52
|
| "But please do this on a system that you can rebuild if things really go wrong"You can count on that! Thanks ... I may be gone some time!Kristen |
|
|
|
|
|
|
|
|
Automated checkdb