SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Consistency error in msdb..sysdtssteplog.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

readysetstop
Posting Yak Master

USA
123 Posts

Posted - 04/17/2007 :  12:01:44  Show Profile  Visit readysetstop's Homepage  Send readysetstop a Yahoo! Message  Reply with Quote
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

USA
899 Posts

Posted - 04/17/2007 :  12:43:40  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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
Go to Top of Page

readysetstop
Posting Yak Master

USA
123 Posts

Posted - 04/17/2007 :  13:01:30  Show Profile  Visit readysetstop's Homepage  Send readysetstop a Yahoo! Message  Reply with Quote
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?
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 04/17/2007 :  13:06:31  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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
Go to Top of Page

readysetstop
Posting Yak Master

USA
123 Posts

Posted - 04/17/2007 :  13:14:07  Show Profile  Visit readysetstop's Homepage  Send readysetstop a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 04/17/2007 :  18:59:21  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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
Go to Top of Page

readysetstop
Posting Yak Master

USA
123 Posts

Posted - 04/17/2007 :  23:29:25  Show Profile  Visit readysetstop's Homepage  Send readysetstop a Yahoo! Message  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000