May 22, 2009 at 11:09 am
Hello,
I recently upgraded our database from SQL 2000 to SQL 2005, and I just noticed that the DB Maintenance Plan that backs up the database has been failing ever since I did the upgrade. The log file shows this:
------------------------------------
Starting maintenance plan 'DB Maintenance Plan1' on 5/22/2009 5:00:03 AM
[1] Database XX: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8992: [Microsoft][ODBC SQL Server Driver][SQL Server]Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=475316578,referenced_minor_id=1) of row (class=0,object_id=1153659078,column_id=0,referenced_major_id=475316578,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=475316578,column_id=1) in sys.columns.
[Microsoft][ODBC SQL Server Driver][SQL Server]Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=475316578,referenced_minor_id=1) of row (class=0,object_id=1137659021,column_id=0,referenced_major_id=475316578,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=475316578,column_id=1) in sys.columns.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'XX'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=475316578,referenced_minor_id=1) of row (class=0,object_id=1153659078,column_id=0,referenced_major_id=475316578,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=475316578,column_id=1) in sys.columns.
[Microsoft][ODBC SQL Server Driver][SQL Server]Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=475316578,referenced_minor_id=1) of row (class=0,object_id=1137659021,column_id=0,referenced_major_id=475316578,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=475316578,column_id=1) in sys.columns.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'XX'.
** Execution Time: 0 hrs, 4 mins, 16 secs **
[2] Database XX: Database Backup...
The backup was not performed since data verification errors were found.
End of maintenance plan 'DB Maintenance Plan1' on 5/22/2009 5:04:19 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
-------------------------------------
I read some posts across the internet, and I got as far as doing this:
select object_id, name, type_desc from sys.objects
where object_id in (1153659078 ,1137659021, 475316578 )
as I am assuming that those 3 objects are the ones at fault? or is it just the first two? That's my first question..
Running that query, the first two objects are stored procedures, and the last object_id is a sql_scalar_function.
Can someone point me in the right direction as to how to fix this problem?
Many thanks in advance.
May 22, 2009 at 11:23 am
Can u Run DBCC CHECKDB on ur database and post the results too?Thanks
May 22, 2009 at 11:31 am
Thanks for the reply; can I run that command at any time, without affecting users accessing the DB? or do I have to run it in single-user mode, etc?
May 22, 2009 at 12:01 pm
Run with Physical_only option first. If time to execute this query is not much (around 3 to 4 mins), then u can go for DBCC CHECKDB to get more complete results
DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY;
DBCC CHECKDB ('YourDatabase');
May 22, 2009 at 12:04 pm
You dont have to run in single user mode when executing above two commands. Single user mode option is used when u r trying to repair the pages. i.e. when u use REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUID. Bur never run them(Repair) till u have a current backup of ur database.
May 22, 2009 at 12:14 pm
Okay, great. Because the DB is located at a retail location, I'll wait until after business hours, run the commands, and then post the results.
May 22, 2009 at 2:12 pm
Since these errors are showing up after upgrading from 2000 to 2005 - I am betting that they existed in 2000 also.
These types of errors are generally a result of somebody manually editing system tables on 2000 and missing rows in the dependencies table.
In SQL Server 2005 - CHECKDB performs additional checks and that is why they are now showing up. To fix these issues, drop and recreate the stored procedures and function. After that, run a DBCC CHECKDB({your db here}) WITH DATA_PURITY. Once that completes successfully, you can go back to normal integrity checks.
Edit: boy, sometimes I wonder if I can even type :hehe:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2009 at 2:31 pm
These errors are the result of someone directly editing the system tables on SQL 2000. They are not the result of the upgrade.
In SQL 2000, CheckDB did not include a check of the system catalog, in 2005 it does. Those errors have probably been around for quite some time.
These errors cannot be repaired by checkDB.
Seeing as the objects are procedures and functions, drop them and recreate them. That should fix the dependency errors. Then run checkDB again and make sure there are no more problems.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Once you're sure it is clean, then run it again with the Data Purity option that Jeffrey mentioned.
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 22, 2009 at 2:48 pm
Thanks for the advice, it is greatly appreciated. One question -- how do I properly drop and recreate the stored procedures? I mean, what is the process one would normally take?
Thanks again.
May 22, 2009 at 3:03 pm
Saam Tariverdi (5/22/2009)
Thanks for the advice, it is greatly appreciated. One question -- how do I properly drop and recreate the stored procedures? I mean, what is the process one would normally take?Thanks again.
The first thing I would do is make sure I knew what privileges/permissions are setup on those procedures. Next, I would right-click on the procedure in SSMS and select modify. This should create a script that would alter your procedure and it should include the permissions. Verify the permissions are correct, change the alter to a create and drop the existing procedure. Once the procedure is dropped - execute the create statement you have modified.
Most importantly, I would recommend performing this on a copy of the database and testing it thoroughly before you do this on your live production system. Once you are comfortable with the changes, and have verified that they solve the problem - schedule a time to make the changes when you can make sure the system is minimally used or you can get exclusive access.
Again - I highly recommend doing this to a ***copy*** of the database first.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2009 at 3:39 pm
You guys are awesome; I recreated those faulty functions/procedures, ran dbcc checkdb with_purity and everything works great now!
Many thanks again for all the help.
May 24, 2009 at 3:44 pm
Glad we could help and happy to hear that you have completed your integrity checks cleanly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2009 at 3:55 pm
Excellent.
Now make sure that you have regular integrity checks scheduled. No need to specify data purity in them, it's a default once it's run successfully once.
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 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply