August 27, 2006 at 10:58 pm
Hi Gurus,
In one of my server, while executing DBCC Checkdb for a database, it is throwing the below error. If anybody experienced this error, please share your thoughts, that would be highly appreciated.
Error details
Msg 8992
, Level 16, State 1, Line 1
Check
Catalog Msg 3853, State 1:
Attribute
(referenced_major_id=982294559,referenced_minor_id=1) of row
(
class=0,object_id=1398296041,column_id=0,referenced_major_id=982294559,referenced_minor_id=1)
in
sys.sql_dependencies does not have a matching row (object_id=982294559,column_id=1)
in
sys.columns.
thanks in advance to all.
warmest
Jayakumar K
Thanks
Jay
http://www.sqldbops.com
August 29, 2006 at 1:27 pm
This is an open wide error which can point to Users Role/Login, Re-complie of OLD SP, DB_OWNER role is missing when you migrate/transfer Database/tables from SQL 2000. worst come you have to re-create the database from scratch and then manually reload tables. I hope this will be helpfull.
Thanks
August 30, 2006 at 9:50 am
Yes, but is there a way to fix it? I'm struggling with 4 of these errors at the moment in a database I recently migrated from SQL2000. Odd thing is, it passes the consistency check fine on that platform.
In SQL2000, I'd simply take a peek at sysdepends and blow away the 4 rows it's complaining about. No can do anymore...
Msg 8992
, Level 16, State 1, Line 1Check
Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1835153583,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.Msg 8992
, Level 16, State 1, Line 1Check
Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1851153640,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.Msg 8992
, Level 16, State 1, Line 1Check
Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1867153697,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.Msg 8992
, Level 16, State 1, Line 1Check
Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1883153754,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.
I first did some research:
select
* from sys.sql_dependencies where referenced_major_id=1941581955 and referenced_minor_id=27
Then tried to do cleanup for one particular row:
delete
sysdepends where [id]=1851153640 and depid=1941581955 and depnumber=27
Error!
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
I tried a suggestion I found with a Google search, 'exec sp_configure 'allow updates', 1' followed by 'reconfigure with override', but no luck...
These DMVs and such in SQL2005 are really starting to agitate me! Running a DBCC CHECKDB with either REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS don't fix the underlying problem.
Any suggestions?
September 1, 2006 at 7:42 am
The saga continues... now the job "succeeds", but clearly doesn't do anything, per the fact that no backups get created and the message in the log:
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2153
Report was generated on "wimkesql04".
Maintenance Plan: Database Backups
Duration: 00:00:01
Status: Warning: One or more tasks failed..
Details:
Check Database Integrity Task (wimkesql04)
Check Database integrity on Local server connection
Databases: All user databases
Include indexes
Task start: 8/31/2006 11:30 PM.
Task end: 8/31/2006 11:30 PM.
Failed: (0) Alter failed for Server 'wimkesql04'.
I don't know what to be more alarmed about - the fact that a job can show a successful finish when that's not the case (very alarming - can I trust SQL Server 2005?) or the fact that it just started working differently from one day to the next.
Did I mention this is a simple Maintenance Plan, first step does an integrity check, second step does a shrink, and third step does a backup - all User databases.
September 3, 2006 at 7:11 am
Jay.
In SQL 2005, checkdb is more strict then in SQL 2000.
Please note that 'exec sp_configure 'allow updates', 1' is not allowed in SQL 2005.
To move forward, what are objects? Please run below queries.
select
object_id, name, type_desc from sys.objects
where
object_id in (1941581955 , 1835153583)
contact me at offline at bmlakhani@yahoo.com
September 3, 2006 at 7:30 am
Another question! was this database restored from SQL 2000 backup?
September 5, 2006 at 7:37 am
I can't speak for Jay, but I know that was the case for my database that was having the issue. Is there anything other than setting the Compatibility Level from 8 to 9?
September 5, 2006 at 8:17 am
Chad,
DBCC CHECKDB runs DBCC CHECKCATALOG also in SQL 2005.
As per my understanding there is some reference for columns in stored procedure which are not available.
Its my assumption. To prove the theory, I need to wait for Jay's response to confirm this
-B
December 26, 2006 at 5:18 pm
Jay,
Did you ever resolve your Check Catalog Msg 3853 error reported in SQLServerCentral please? I'm having the same problem and would appreciate your advice? Please could you update your post?
December 27, 2006 at 2:00 am
Hi Adrian,
Before come to a resolution for that issue I switched from the company in the same month.
Appologies for the inconvenience caused...
warmest
Jay.
Thanks
Jay
http://www.sqldbops.com
December 27, 2006 at 4:09 pm
Thanks Jay.
I've opened a call with Microsoft and will try to remember to post an update when available.
February 1, 2007 at 8:10 pm
Microsoft used the references in my error message from this problem to identify a related stored procedure that was pointing to columns in table(s) that no longer existed. The stored procedure was recompiled and the problem went away. Checkdb runs successfully now. As refected in prior messages, I think checkdb is more thorough in 2005 and picks up "errors" such as these.
September 17, 2007 at 5:17 pm
In my case, the consistency error affects a view. I dropped and recreated the view, and then ran DBCC CHECKDB on the database again, and the eorr is now gone.
So check the object affected and recreate it.
September 23, 2007 at 4:51 am
I think you'll find the first occurring object number relates to a stored procedure. If you recompile that stored procedure, the required system columns are populated after a migration, and th eerror goes away.
October 4, 2007 at 2:16 am
Hi im having the same problem,
How can i find out which View or Stored Procedure is incorrect
My Error:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=11199140,referenced_minor_id=28) of row (class=0,object_id=27199197,column_id=0,referenced_major_id=11199140,referenced_minor_id=28) in sys.sql_dependencies does not have a matching row (object_id=11199140,column_id=28) in sys.columns.
Thanks
George
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply