SQL 2005 CHECKDB error in sys.sysschobjs

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

SQL 2005 CHECKDB error in sys.sysschobjs

Author

Topic

RickNSQ
Starting Member

4 Posts

Posted - 2007-03-26 : 13:21:54

Hi,

We are running 2005, 9.00.2047.00, and getting a consistency error on sys.sysschobjs. The system is running fine as far as I can tell. I don't see any documentation on how to repair the object, nor can I access it directly. Any suggestions/procedures on how fix it? If I leave it the way it is will what are the ramifications later on? Please find more detail on the CHECKDB error below.

Thanks for the help.

Rick

======================================================================

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Msg 2570, Sev 16, State 3, Line 17 : Page (1:1103587), slot 24 in object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type "In-row data"). Column "modified" value is out of range for data type "datetime". Update column to a legal value. [SQLSTATE 42000] Msg 8990, Sev 16, State 1, Line 17 : CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysschobjs' (object ID 34). [SQLSTATE 01000] Msg 8989, Sev 16, State 1, Line 17 : CHECKDB found 0 allocation errors and 1 consistency errors in database 'DV1'. [SQLSTATE 01000]

Job 'Check Database DV1 [20060816141231-0-030000]' : Step 1, 'Step1' : Began Executing 2006-08-18 03:00:00

DBCC CHECKDB (DV1) started at Aug 18 2006 3:00AM with Logfile: F:\Program Files\Microsoft SQL Server\MSSQL.1

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-26 : 13:36:10

Is this a database that was upgraded from SQL 2000?

If not, has anyone modified that system table directly? (Yes, its possible in 2005 but only using undocumented means). If not, when did the corruption occur (i.e. between regular CHECKDBs or is this the first CHECKDB in a long time)? Are there any signs of h/w issues in the SQL Server error log or Windows event logs?

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

RickNSQ
Starting Member

4 Posts

Posted - 2007-03-26 : 14:31:37

Thanks Paul for responding so quickly.

This is a new install of MSSQL 9.0. I don't see any errors on the disk drives.
As far as I know, no one has updated the table sys.sysschobjs or would have need to.

What do you think I should do?

Regards,

Rick

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-26 : 15:29:30

Hmm. I don't see any known issues in our bug databases - I'm checking with the metadata dev team to make sure and I'll 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

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-03-27 : 00:48:50

Is Create_data and Modify_data columns has the same value in sys.objects table for sysschobjs? If the object was update, I believe we should see different date in modify_date column...

select * from sys.objects where name like '%sysschobjs%'

MohammedU

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-27 : 01:22:21

The dev team are still checkig for issues. In the meantime, they suggest that you can get rid of the issue by doing something like rebuilding (if its a table) or drop/recreate if its a T-SQL module.

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

RickNSQ
Starting Member

4 Posts

Posted - 2007-03-27 : 11:44:29

Hi Paul

I don't know how to drop and rebuild the object sys.sysschobjs. Can I simply just reapply the support pack? The one we originally used was SQLServer2005SP1-KB913090-x86-ENU. Is this what you mean?

I ran the following as suggested by MohammedU above; but I believe the dates are correct. The modify_date is the date we did the support pack...SQLServer2005SP1-KB913090-x86-ENU, so I would expect modify_date to change...yes?

select name, create_date, modify_date from sys.objects where name like '%sysschobjs%'

Name Create Date Modify Date
---------- ---------------------- -----------------------
sysschobjs 2005-10-14 01:36:15.987 2006-07-23 22:00:13.080

Please Advise.

Rick

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-28 : 08:35:10

(Sorry for the delayed reply - I'm at the SQL Connections conference in Orlando and didn't have a chance to get online yesterday).

I was unclear - I mean drop/recreate the table referenced in the corrupt row in sysschobjs. You'll need to use DBCC PAGE to do this.

DBCC TRACEON (3604)
GO
DBCC PAGE (yourdb, 1, 1103587, 3)
GO

and look at slot 24. Can you post the results?

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

RickNSQ
Starting Member

4 Posts

Posted - 2007-04-05 : 17:39:26

Hi Paul,

I ran the query above as you suggested and the results are posted below.

----------------------------------------------------------------------------------------
Slot 24 Column 9 Offset 0x1c Length 8

created = Jul 23 2006 11:59PM

Slot 24 Column 10 Offset 0x24 Length 8

modified = Jul 23 2006 12:00PM

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-05 : 17:51:21

Can you post all the column output for that slot?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources