July 10, 2012 at 2:43 pm
Please pardon any ignorance on my part...(brand-spanking-new-DBA)
Running CheckDB returns the following error:
Check Catalog Msg 3853, State 1: Attribute (object_id=1357963914) of row (object_id=1357963914,column_id=1) in sys.columns does not have a matching row (object_id=1357963914) in sys.objects. [SQLSTATE 42000] (Error 8992) CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. [SQLSTATE 01000] (Error 8954) CHECKDB found 0 allocation errors and 1 consistency errors in database 'amtslab'. [SQLSTATE 01000] (Error 8989). The step failed.
I found this article: http://www.sqlservercentral.com/articles/Corruption/69382/
The method preferred by most of the SQL corruption gurus is to create another database, and copy the contents of the corrupted database into the new database. Depending on the size of your database, this could take quite some time.
Could you tell me...what is involved with "copying the contents of the corrupted db to the new db?" Is the article referring to manually transferring all the data and objects? If so, is there any tool that could assist?
Also, this error apparently has been happening for quite some time (the server was upgraded some time ago and we just began recently to run CheckDB) and all copies of this db on our other intances and all backups copies appear to have this issue. Is it something that MUST be repaired? If it is something we can get by with, should and could we repress the errors so CheckDB could continue?
Also, when I query sys.columns and sys.objects for object_id 1357963914 I do not get any records returned.
Any information you have to offer would be greatly appreciated. TIA!
July 10, 2012 at 2:49 pm
sounds like two things: this database was upgraded from SQL 2000, and someone had ran "EXEC master.dbo.sp_configure 'allow updates', '1'", and deleted the row from sysobjects directly, without deleting the related data in syscolumns.
2005 and above don't allow direct editing any more, so it's rather difficult to clean up.
Lowell
July 10, 2012 at 2:58 pm
Please run the following and post the full and complete results.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Yes, it's something you need to repair.
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
July 10, 2012 at 3:05 pm
The error pasted above is the result of running:
DBCC CheckDB
WITH ALL_ERRORMSGS, NO_INFOMSGS;
Go
It is run within a job, so the Database dropdown is set to the db name amtslab (as opposed to putting it in the code). Here it is again:
Date7/8/2012 10:00:01 PM
LogJob History (Maintenance: Weekly Overhaul)
Step ID1
ServerDEV-SR\DEV
Job NameMaintenance: Weekly Overhaul
Step NameCheckDB: amtslab
Duration00:14:51
Sql Severity16
Sql Message ID8989
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: OurCompany\sqladmin. Check Catalog Msg 3853, State 1: Attribute (object_id=1357963914) of row (object_id=1357963914,column_id=1) in sys.columns does not have a matching row (object_id=1357963914) in sys.objects. [SQLSTATE 42000] (Error 8992) CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. [SQLSTATE 01000] (Error 8954) CHECKDB found 0 allocation errors and 1 consistency errors in database 'amtslab'. [SQLSTATE 01000] (Error 8989). The step failed.
July 10, 2012 at 3:25 pm
Good, just the one error.
Ok, two options for fixing this.
Option 1 will be a lot of work, take quite a bit of time, but safe
Option 2 will be quick to do, but if you make a mistake you will likely damage the database more.
Pick your poison... 🙂
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
July 11, 2012 at 6:33 am
Could you tell me...what is involved with "copying the contents of the corrupted db to the new db?" Is the article referring to manually transferring all the data and objects? If so, is there any tool that could assist?
Thanks, Gail...Do you have any insite on what's involved with option one? This is actually happening on 4 or 5 dbs.
July 11, 2012 at 6:56 am
Option 1: Script all objects, export all data (bcp out or SSIS), recreate the database. Yes, it's a lot of work.
Option 2: hack some more at the system tables.
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
July 11, 2012 at 7:05 am
Thanks much for for taking the time to give me something to go on! You Rock! 😀
July 11, 2012 at 7:28 am
p.s. If you decide to hack the system tables, post back and I'll walk you through step by step. It's too risky to have a go at if you aren't completely sure how to do.
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
July 11, 2012 at 8:38 am
lisa.randles (7/10/2012)
Also, when I query sys.columns and sys.objects for object_id 1357963914 I do not get any records returned.Any information you have to offer would be greatly appreciated. TIA!
No records at all for this object id in sys.objects or sys.columns?
Sounds like a whole table was hacked out and left a rogue column behind, otherwise you would at least expect to see
a\ the table object in sys.objects
b\ a bunch of other column ids in sys.columns
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 8:56 am
Appologies, Perry, you are correct. I found the records...I was inadvertantly looking at the master sys.objects and not the db.sys.objects. (~blush~)
For anyone else looking for info on this issue, I just found this post a bit ago: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119501. They were able to determine the offensive objects and resolve the issue... I don't think I can do so, though, because the object records are the ones that are missing. The link also has Paul Randal's instruction for "option 2". If this is the route we choose, I (newb) won't be the one deleting the records...but thanks for the offer, GilaMonster! You're a peach!
July 11, 2012 at 9:01 am
Sorry! (double-post)
July 11, 2012 at 9:46 am
Here is what I did to resolve mine. I've used these steps several times for "check catalog msg 3853".
Before you start deleting anything please bear in mind that no one understands your product better than yourself so do check with the developers.
1.Backup the corrupt database and apply steps two to six, when successful re-apply to your live db.
2.Point your query analyser to the corrupt database and run “sp_helptest dbcc checkdb” this may take awhile, a good time gauge is how long it takes for your integrity checks to run.
3.Get all the listed “referenced_major_id” and “object id’s” from the error msg using the script here...
Select object_id,name, type_desc
From sys.objects
Where object_id in (referenced_major_id, object_id)
4.List all the names, drop and recreate any sp’s and functions. Any found tables do check with your developers first.
If any of the listed tables contains data please don’t delete
5.Run dbcc checkdb (your db name here) with data_purity
6.Run integrity checks
7.If successful apply the same steps to the live database
July 11, 2012 at 9:53 am
wampiah (7/11/2012)
Here is what I did to resolve mine. I've used these steps several times for "check catalog msg 3853".Before you start deleting anything please bear in mind that no one understands your product better than yourself so do check with the developers.
1.Backup the corrupt database and apply steps two to six, when successful re-apply to your live db.
2.Point your query analyser to the corrupt database and run “sp_helptest dbcc checkdb” this may take awhile, a good time gauge is how long it takes for your integrity checks to run.
3.Get all the listed “referenced_major_id” and “object id’s” from the error msg using the script here...
Select object_id,name, type_desc
From sys.objects
Where object_id in (referenced_major_id, object_id)
4.List all the names, drop and recreate any sp’s and functions. Any found tables do check with your developers first.
If any of the listed tables contains data please don’t delete
5.Run dbcc checkdb (your db name here) with data_purity
6.Run integrity checks
7.If successful apply the same steps to the live database
That's for a different type of metadata problem, one where there are orphans in sys.dependencies. It's not going to help here when the orphaned rows are in sys.columns.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply