November 8, 2011 at 3:07 pm
I have DBCC consistency check failure on one my DB since it was migrated from 2k.
It boils down to the following :
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.
More exactly 6 of the sys.columns rows refer to a parent object ID called 780685979 which apparently does not exist in sys.objects.
So I tried the following
INSERT INTO sys.objects VALUES('myobjectname',780685979,NULL,1,0,'U','USER_TABLE','2002-01-02 10:17:42.247','2002-01-02 10:17:42.247',0,0,0)
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
sp_configure "Allow Updates", 1
GO
--Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
reconfigure with override
GO
--Command(s) completed successfully.
The thing is when I configure the server to allow updates , it says successfully completed, but when I to re-run the insert statement it errors out again with same error
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
What are my options here, how do I get this row inserted into sys.objects so that it passes the consistency check ?
I heard we can do it thru a dedicated admin connection, is it so ? if so how?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
November 8, 2011 at 3:28 pm
Don't hack the system tables, it's likely what caused this in the first place and if you don't know what you're doing you could make things far worse. sys.objects isn't even a table. The real system tables are completely undocumented.
My guess, this DB was updated from SQL 2000 and someone tried a delete from sysobjects without understanding the consequences.
Script all objects out.
Export all data
Recreate the database.
Safest fix for this.
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
November 8, 2011 at 3:32 pm
Cannot be stressed enough the cautions Gail threw your way on this. Do not attempt to edit sys.objects
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 9, 2011 at 10:05 am
Theoretically (I'm not advocating doing it whatsoever), would it be possible to create an object, check if it has object_id = 780685979 then delete it?
Or would the extant rows in sys.columns (or the actual underlying system table) prevent that particular object_id being reused?
The ability to directly update system tables was banned from SQL 2005 onwards no?
November 9, 2011 at 10:11 am
Gazareth (11/9/2011)
Theoretically (I'm not advocating doing it whatsoever), would it be possible to create an object, check if it has object_id = 780685979 then delete it?
It might, might, might, assuming you can force an object id...
Whether dropping such an object would fix the orphaned rows or not though is another question.
The ability to directly update system tables was banned from SQL 2005 onwards no?
Yup. Allow updates does nothing except throw an error now.
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
November 9, 2011 at 10:24 am
Yep I agree, 99% of times users were suggested NOT TO INTERFERE with system tables in all da forums out there.
But I scripted the trouble some system table and recreated with new name just to see how it populated sys.objects and sys.columns, I have 4 columns with a PK and a Default Constraints.
When I compare the old and new objects in both sys.objects and sys.columns, its so convincing that what all the sysobject table[view] needs is a reference to the missing row which contains parent_object_id of those 4 columns in sys.columns.
Anyways I tried to go
sqlcmd -E -S SERVERNAME -A -- A dedicated Admin connection
Tried running that insert statement
and it failed.
SO as of now i scripted out the data along with all objects ...!
have a whooping 3.2 GB sql file ...
Trying to figure out how to run this script .. will have questions .. soon
Will keep u posted guys.. Thanks for the input.
SQLSERVERCENTRAL ROCKS 🙂
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
November 9, 2011 at 11:03 am
Minnesota - Viking (11/9/2011)
When I compare the old and new objects in both sys.objects and sys.columns, its so convincing that what all the sysobject table[view] needs is a reference to the missing row which contains parent_object_id of those 4 columns in sys.columns.
No, because there's no way you'd be able to work out all the correct values to insert into the base tables. Sys.objects is not a table. To give you an idea, one of the base tables has the following columns:
auid, type, ownerid, status, fgid, pgfirst, pgroot, pgfirstiam, pcused, pcdata, pcreserved, dbfragid
What do you insert for those?
To fix this by hacking the system tables, you'd have to delete the orphaned rows from the base tables, and there's about 4 of them, all completely undocumented and barely known.
Let me put it this way. I do know enough about the system tables that I could possibly fix a problem like this with direct updates. If I had a client with this error, I'd fix it with the script, export, recreate method.
btw, I didn't say script the data. Script the objects, bcp out the data. Otherwise the resultant file can get insane on larger DBs.
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
November 10, 2011 at 12:56 am
Yep I agree, 99% of times users were suggested NOT TO INTERFERE with system tables in all da forums out there.
One of the reasons for it is you won't get support from MS if your server crashes.
You bought the licenses to use SQL Server objects. You don’t suppose to hack it for any damn reason.
November 10, 2011 at 3:13 am
When I compare the old and new objects in both sys.objects and sys.columns, its so convincing that what all the sysobject table[view] needs is a reference to the missing row which contains parent_object_id of those 4 columns in sys.columns.
That's the problem - it is convincing. But it's not the full story 🙂
November 10, 2011 at 8:24 am
Any Ideas as to how to SCRIPT a encrypted VIEW or for that matter any object ?
my Generate script task stops coz it cant script one of the views in DB which is Encrypted.
What are my options here?
I'm logged in as SA
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
November 10, 2011 at 8:26 am
Gazareth (11/10/2011)
When I compare the old and new objects in both sys.objects and sys.columns, its so convincing that what all the sysobject table[view] needs is a reference to the missing row which contains parent_object_id of those 4 columns in sys.columns.
That's the problem - it is convincing. But it's not the full story 🙂
No support han ..?
This is some scary stuff, OK I think I have adequate reason to curb my fancy thoughts of editing system views(:-P)
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
November 10, 2011 at 8:27 am
Google decrypt sql server. There's plenty of ways to do that.
Just make sure it's legal for you (vendor contracts).
November 10, 2011 at 8:27 am
Minnesota - Viking (11/10/2011)
Any Ideas as to how to SCRIPT a encrypted VIEW or for that matter any object ?my Generate script task stops coz it cant script one of the views in DB which is Encrypted.
RedGate SQLPrompt (professional) can script encrypted views.
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
November 10, 2011 at 8:29 am
Minnesota - Viking (11/10/2011)
Gazareth (11/10/2011)
When I compare the old and new objects in both sys.objects and sys.columns, its so convincing that what all the sysobject table[view] needs is a reference to the missing row which contains parent_object_id of those 4 columns in sys.columns.
That's the problem - it is convincing. But it's not the full story 🙂
No support han ..?
None whatsoever. Mess with the system tables and it's flagged in the database header permanently (and noted in the error log after every restart). CSS won't offer any assistance, they'd probably tell you to do what I've told you to do and then call them back.
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
November 10, 2011 at 8:36 am
GilaMonster (11/10/2011)
Minnesota - Viking (11/10/2011)
Any Ideas as to how to SCRIPT a encrypted VIEW or for that matter any object ?my Generate script task stops coz it cant script one of the views in DB which is Encrypted.
RedGate SQLPrompt (professional) can script encrypted views.
How it is possible? The encryption logic resides in SQL Server metadata & binaries. How can a third party tool get the decryption logic for the same?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply