May 14, 2010 at 8:39 am
I got a consistency error alert this morning for one of my databases. Below is the specific error message:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=210099789) of row (object_id=226099846) in sys.objects has a matching row (object_id=210099789) in sys.objects (type=S ) that is invalid.
Usually I am use to seeing consistency errors that suggest the type of repair to attempt to recovery the data if possible. Can anyone please advise as to how to handle the above error in respect to attempting to recover the data? I do have backups but it will take couple of days plus a stock pile of paper work to go down that path.
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 14, 2010 at 9:03 am
Have you run an integrity check?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 9:06 am
Read this, it would indicate a metadate error
http://www.sqlservercentral.com/articles/Corruption/65804/
this article links to this
Paul Randal is the man. He wrote DBCC CHECKDB, I would treat whatever he says as gold.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 12:43 pm
Bradley,
Thanks a million for the links - great pointers!!
Yes Paul is "de MAN" on this topic and I should have visited his blog before posting. I will implement some of the suggested solutions in the AM and let you know how things turn out.
Thanks man....
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 14, 2010 at 12:49 pm
Glad to hear it!
The team I used to work for had this same issue a couple months after I had left, and I had a drive fall out on a RAID configuration recently and got some corruption errors.
Yeah I dream to have a resume that reads 1/4 as nice as Paul's, and Gail is amazing as well her blog is http://sqlinthewild.co.za/
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 1:31 pm
That error's not repairable. This is a 2005 server? CheckDB has been succeeding until recently? I ask, cause this is usually the result of someone doing ad-hoc direct updates to the system tables, typically in SQL 2000 prior to a 2005 upgrade.
Can you check the error log, see if there's any mention that the catalog/system tables in that database have been directly updated. Error will appear right at the point SQL brings the DB online.
Can you run the following and post the full and complete output?
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
As for following Paul's blog, I would strongly advise that you do not make any changes to the system tables at the moment. This is not exactly the same error as he talks about there (he's talking about missing references, this is an incorrect reference), and one of the two objects appears to be a system object.
Hacking the system tables without knowing exactly what you're doing can result in worse damage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2010 at 1:36 pm
What are objects 226099846 and 210099789?
SELECT object_name(226099846)
SELECT object_name(210099789)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2010 at 8:17 pm
Gail, thanks for taking a look. Below is the information you requested:
DBCC CHECKDB (Deployment_09_24_09) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 8992, Level 16, State 1, Line 3
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=210099789) of row (object_id=226099846) in sys.objects has a matching row (object_id=210099789) in sys.objects (type=S ) that is invalid.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'mydatabase'.
SELECT object_name(226099846)
UQ__syskeys__0D7A0286
(1 row(s) affected)
SELECT object_name(210099789)
syskeys
(1 row(s) affected)
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 17, 2010 at 1:03 am
This is a SQL 2005 server?
What's the type of syskeys?
SELECT type_desc from sys.objects where name = 'syskeys'
Some other important questions.
Has checkDB been running successfully up until now?
Is this DB an upgraded DB from SQL 2000?
Are there any messages in the error log about direct updates to the system catalog? Would appear right after SQL brings the DB online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2010 at 10:42 am
It is a SQL 2005 Server
The DB is an upgraded DB from SQL 2000
The type for syskeys is SYSTEM_TABLE
My understanding is that CHECKDB was not running in the past
There are no messages about Direct Updates to the system catalog. Will this message show if those updates occurred in the past?
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 17, 2010 at 6:36 pm
Sounds like someone may have messed with the system tables back on SQL 2000 and no one caught it. Odd, syskeys isn't even in any of my SQL 2005 database, let alone as a system table.
You have two options here.
1) Recommended. Script all objects, export all data, recreate the database
2) Hack the 2005 system tables so that they are consistent. I have no idea which tables you'll have to change and what you will have to do to them to get rid of this problem. Note that sys.objects is not a system table. It's a view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2010 at 8:08 pm
You need to run these two queries which will give you an idea of hat data is corrupt. The records are there, it just looks like a type value has been corrupted. These will return the parent and child records. Post the results and we may be able to help.
select * from sys.objects where parent_object_id=210099789
go
select * from sys.objects where object_id=210099789
go
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 18, 2010 at 3:45 pm
Gail, thanks for your time and input. I have a meeting with my boss on Friday and I will present both recommendations - I will keep you posted. Mainwhile I have began researching how to hack system tables in 2005.
Leo, thanks for your input as well.
Gail, while I have your attention and since the topic is the same, I ran into more DBCC errors on another environment I'm now in charge of. Just a little history here, my team began providing hosting assistance to one of our dev groups and I am doing an inventory of their manintenance strategy.
There DBCC CHECKDB errors are below:
" failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 68523049811968, alloc unit ID 68523049811968 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1045578763).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
" failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 98479666036736, alloc unit ID 98479666036736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1502680451).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Having read your blog is it safe to run the recommended DBCC UPDATEUSAGE command and these issues should be resolved?
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 18, 2010 at 10:49 pm
cos_ta393 (5/18/2010)
Gail, thanks for your time and input. I have a meeting with my boss on Friday and I will present both recommendations - I will keep you posted. Mainwhile I have began researching how to hack system tables in 2005.
Just a word of advice, if I were in your situation, I'd be looking at scripting and exporting, not hacking. Also note that updating the system tables directly will lose you all support from MS for this database (and that info is stored within the DB and logged in the error log whenever the DB is brought online)
" failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 68523049811968, alloc unit ID 68523049811968 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1045578763).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/ That particular error is one of the ones detailed in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2010 at 1:10 am
Thanks for your advise - really appreciated.
And yes I read your article and have successfully resolved those errors.
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply