DBCC Errors on sys.sysschobjs - SQL Server Forums

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

DBCC Errors on sys.sysschobjs

Author

Topic

BranBuds
Starting Member

5 Posts

Posted - 2008-09-03 : 11:00:21

I run a weekly consistency check on a database that has been up and running for a couple of years. Last weekend it failed for the first time. I ran repairs will allow data loss and have been successful in gettin my data back. I still am getting an error on sys.sysschobjs
--------------------------------------
DBCC results for 'sys.sysschobjs'.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data). Page (1:919) was not seen in the scan although its parent (1:45) and previous (1:6286) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data). Page (1:9007) is missing a reference from previous page (1:919). Possible chain linkage problem.
-------------------------------------

Does anyone have any ideas on how to clear this out?

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-09-03 : 11:15:31

Do you have a backup you can restore from? I'm guessing not since you ran REPAIR_ALLOW_DATA_LOSS. Do you know what the other errors were (if any)? Most likely you lost data when you ran repair.

Can you give the complete output from DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS? I want to see if it gives any info about whether it can repair that error or not - as far as I remember, that's one of the system tables I coded it not to be able to repair (in which case you must restore or extract the remaining data into a new database).

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

BranBuds
Starting Member

5 Posts

Posted - 2008-09-03 : 11:34:28

Paul,
Thanks for the quick response. Here is the ouput you requested.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data). Page (1:919) was not seen in the scan although its parent (1:45) and previous (1:6286) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data). Page (1:9007) is missing a reference from previous page (1:919). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.sysschobjs' (object ID 34).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Viewpoint'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Viewpoint).

I did not lose very much data and was able to recreate it from other sources. Took all night but hey. Going back to a backup that did not have the issue was not an option for me. I went back as far as last Friday and that database restore had the same issues.

I know that my previous Saturday night backup was good as the following morning the db passed consistency checks. I may have a more recent one though.

Thanks

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-09-03 : 15:06:36

From the ouput provided, you should run DBCC CHECKDB 'dbname' repair_rebuild (see the last line of your output "repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB"). It appears the issues are with indexes and the repair_rebuild option will rebuild indexes, among other things. This can be done without risk of data loss (see BOL).

Terry

BranBuds
Starting Member

5 Posts

Posted - 2008-09-03 : 15:49:52

I had tried that but it tells me that it cannot be repaired.

I also appear to have a damaged Stored Proc that I cannot open, rename or delete

Alter runs without error but the same sympthoms apply after alter

BranBuds
Starting Member

5 Posts

Posted - 2008-09-03 : 15:51:37

Also how do I tell what Object ID 34 refers to

BranBuds
Starting Member

5 Posts

Posted - 2008-09-03 : 16:08:21

OK I Ran a recreate statement for the stored proc and it gave me an error stating that the object already exists but it resolves the issue. I just have to reset execute permissions.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-09-04 : 02:42:44

The REPAIR_REBUILD suggestion is a red-herring unfortunately - the table in question is a system table and CHECKDB won't touch it, even though the corruptions are just in the clustered index b-tree.

Did creating the new sproc cause the corruption errors to go away? If so, I'd guess that the b-tree links were 'fixed' when a new page was linked in to accomodate the metadata for the new sproc.

System table corruptions are always dodgy to work around if you don't have backups - see [url]http://www.sqlskills.com/blogs/paul/2008/06/12/TechEdDemoUsingTheSQL2005DedicatedAdminConnectionToFixMsg8992CorruptSystemTables.aspx[/url] for an example.

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources