How can I generate call stack on error on the fly

Question

I have an issue with an index on a Sql2k server:

Event Log shows the following errors happening:

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Error: 644, Severity: 21, State: 5

and then ...

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Could not find the index entry for RID '16958f0b001e00020000' in index page (4:964157), index ID 74, database 'db'..

I've been chasing this error and have already ruled out http://support.microsoft.com/kb/822747

Then I found the following forum discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60518

My question, I guess, is whether or not anyone knows this "undocumented way to turn on dumping for a particular error." (last forum entry).

Thank you, in advance.

Answer 1

To answer your question: the CSS blog does explain how to turn on dump on specific error: DBCC DUMPTRIGGER('set', 644). They also post a big red warning:

WARNING: The following should always be used with caution and under Microsoft Support supervision and may only be supported when used under such supervision.

Even if you do obtain a dump for error 644 the resulted .mdmp would be rather useless for you, and for anybody else for the matter since the error indicates data corruption likely due to media problems, not a product defect. Rebuilding the table, restoring from a good backup, DBCC CHECKDB and the other usual avenues to deal with data corruption are the real answer you're looking for.

Answer 2

If the reports are always referring to the same index, then you might just have a corrupt nonclustered index (since the index ID is greater than 1). I'd definitely keep a close eye on hardware diagnostics, and also keep SQL Server up-to-date on patches to identify the root cause of it becoming corrupted. But you may be able to repair the problem by simply rebuilding the index:

DBCC DBREINDEX(table_name, index_name)

It's possible that the corruption is a total fluke (either a software bug, or a hardware error that went undetected), but if it hits again, and corrupts a table/clustered index, rather than just a nonclustered index, then you could have a bigger problem on your hands.