Consistency error in msdb..sysdtssteplog.

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.

All Forums

General SQL Server Forums

Data Corruption Issues

Consistency error in msdb..sysdtssteplog.

Author

Topic

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-17 : 12:01:44

I'm getting the following output from dbcc checktable(sysdtssteplog):

Msg 8928, Level 16, State 1, Line 2
Object ID 594101157, index ID 0: Page (1:744) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 594101157, index ID 0, page (1:744). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 594101157, index ID 1. Page (1:744) was not seen in the scan although its parent (1:4660) and previous (1:607) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 594101157, index ID 1. Page (1:745) is missing a reference from previous page (1:744). Possible chain linkage problem.
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'sysdtssteplog' (object ID 594101157).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (msdb.dbo.sysdtssteplog ).

Is this a DTS package or log file I can just remove, or can I just somehow remove that data from the table? I ask this because all the DTS packages seem to be running (and logging) fine.

Or will this involve restore and replace? What direction should I go from here?

I have not seen any hardware errors on this machine. Yet.

Thanks for any and all help.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-17 : 12:43:40

It's a table that logs the steps in DTS packages. Nothing will go wrong (as long as this is the only page that is corrupt) until something tried to read that page.

Are there any other corruptions in the msdb database (or other databases hosted on the same hardware)? It has the symptoms of being a hardware-caused corruption.

Can you try doing a select * from that table to see what 82x error you get?

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-17 : 13:01:30

Paul,

Thanks for the help. There are no other data corruption issues anywhere else in SQL. I did notice a file access issue on a share on that server many moons ago, but that problem has since disappeared.

Here's the error from the select * statement:

Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000005d0000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\data\msdbdata2.mdf'.

Also, I was able to restore this to another DB (msdb2) and successfully deallocate the page with DBCC CHECKTABLE(sysdtsteplog, repair_allow_data_loss). It all shows up clean now. Am I correct in thinking I can just move this table over into the original MSDB, or is there a better method for doing this? Would I cause any referential issues by doing so?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-17 : 13:06:31

ok - so this is a hardware issue for sure - one of the drives did not completelr write out a page - so the on disk image has some portion of the new page, and some portion of the old page (i.e. its 'torn').

Was there a power outage, or do you have disk caching enabled?

Not sure what effect deleting data from that table will have - let me check and get back to you.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-17 : 13:14:07

Paul,

There were no power outages that I am aware of. There is the standard caching enabled on the RAID card.

Thanks again for your help.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-17 : 18:59:21

I checked with the DTS/SSIS team here and there's no effect on behavior except loss of that portion of the log for you.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-17 : 23:29:25

Excellent. I'll move the corrected table into the existing MSDB in the morning.

Thanks again for all your help. It's nice to have experts available.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources