October 11, 2011 at 10:05 am
I have a 5 Gig DB which is failing DBCC CHECKDB with following errors.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
More Info:
This is on a SQL Server 2008 R2 box
DB compatibility is 2000(80) , Simple Recovery mode.
I LDF and 1 MDF
200 Users.
Observations:
This check was a part of maintenance plan
Tried running it in the query window with the same result.
Didn't use any repair options yet .. !
Looked into the logs and found that this had been failing from the date on which this DB was created on this Server (By created I mean moved from 2000)
Please shed some light as to how to troubleshoot this issue 🙁
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
October 11, 2011 at 10:12 am
Please post any messages in the error log that relate to this DB.
Does this error persist over a SQL restart? Is the database giving any errors when accessing data?
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
October 11, 2011 at 10:31 am
can you provide more details of the move from 2000 to 2008.
Did you run DBCC CHECKDB after the upgrade and did it run successfully?
Did you run DBCC UPDATEUSAGE after the database upgrade?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2011 at 12:46 pm
GilaMonster (10/11/2011)
Please post any messages in the error log that relate to this DB.Does this error persist over a SQL restart? Is the database giving any errors when accessing data?
The error message in the log is not descriptive it just says
Task end: 2011-03-13T06:03:19.
Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'mydbname') WITH NO_INFOMSGS
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SQL restart ? No :-/ I didnt try it its on one of my Prod Boxes.
But i tried to restore a fresh backup as a test DB and run CHKDB on it but no ...
I still get the same error
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
October 11, 2011 at 12:47 pm
Perry Whittle (10/11/2011)
can you provide more details of the move from 2000 to 2008.Did you run DBCC CHECKDB after the upgrade and did it run successfully?
Did you run DBCC UPDATEUSAGE after the database upgrade?
Perry
Like i just found out Its been failing since the day it was created on this 2008 box.
Its a weekly maintenance job. Only this particular DB fails the DBCC check
And NO, I didnt run DBCC UPDATEUSAGE , Should I/Can I run it at-least now ?
May be on the restored test DB, wat say ?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
October 11, 2011 at 2:06 pm
I ran UPDATE USAGE followed by CHECKCATALOG as per its suggestions and found out the following.
Does this help to troubleshoot this issue ?
DBCC UPDATEUSAGE (0);
GO
/*
Msg 211, Level 23, State 192, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
*/
DBCC CHECKCATALOG;
GO
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=1) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=2) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=3) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=4) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=5) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=780685979) of row (object_id=780685979,column_id=6) in sys.columns does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=780685979) of row (object_id=796686036) in sys.objects does not have a matching row (object_id=780685979) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=780685979) of row (object_id=812686093) in sys.objects does not have a matching row (object_id=780685979) in sys.objects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
October 11, 2011 at 2:31 pm
Yup. Someone updated the system tables back on SQL 2000 and stuffed up the integrity of the system tables. There's no fix for this.
Script all objects, export all data, recreate the database. (that's unless going back to the pre-upgrade version is an option)
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
October 11, 2011 at 2:37 pm
GilaMonster (10/11/2011)
Yup. Someone updated the system tables back on SQL 2000 and stuffed up the integrity of the system tables. There's no fix for this.Script all objects, export all data, recreate the database. (that's unless going back to the pre-upgrade version is an option)
Looking at the object IDs say that they are:
DEFAULT_CONSTRAINT and
PRIMARY_KEY_CONSTRAINT
cant i get away by recreating these ?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
October 11, 2011 at 2:39 pm
You can try dropping them both and then recreating them both. It's possible that the drop will fail.
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
October 11, 2011 at 4:59 pm
Minnesota - Viking (10/11/2011)
Looking at the object IDs say that they are:DEFAULT_CONSTRAINT and
PRIMARY_KEY_CONSTRAINT
cant i get away by recreating these ?
The default constraint shouldn't be to much of an issue to re create but the PK may give you some work, shouldn't be impossible but will involve extensive schema mods.
Once you have this fixed run DBCC CHECKDB WITH DATA_PURITY
In SQL 2005 on checkcatalog is run as part of checkdb.
Let us know how you get on 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply