March 13, 2006 at 9:19 am
Hi All,
After upgrading to SQL 2005, a few of my databases are reporting some consistency errors where objects in sys.columns have no match in sys.objects. Is there any way whatsoever to update system tables in SQL 2005? DBCC CHECKDB does not repair the error, even while allowing data loss. Any help would be greatly appreciated!
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=50203329) of row (object_id=50203329,column_id=1) in sys.columns does not have a matching row (object_id=50203329) in sys.objects.
March 16, 2006 at 8:00 am
This was removed by the editor as SPAM
March 23, 2006 at 9:42 am
I had a consistency error that we couldn't figure out recently (actually I'll be posting on that soon), but I did a 'dbcc checkdb (tablename, REPAIR_ALLOW_DATA_LOSS)' and it resolved the consistency error. However, it wiped out my data. It's a good think I was doing this in a backup I had restored. When I ran it on production, I copied the data into another table and then copied it back. And it worked for me.
Good Luck!
March 23, 2006 at 10:15 am
I was able to resolve the consistency errors, but I had to jump through a few hoops to do it. My problem was a few entries in sys.columns were left behind after I delete a table. The entry in sys.objects was deleted successfully, but the entries in sys.columns were left behind. I had to use the new Dedicated Administrator Connection (DAC) feature in 2005. This is the only way I've found to actually make direct changes to the system catalog. To fix the errors, I created a dummy temp table and pointed the orphaned sys.columns entries to the temp table in sys.objects. Then I just deleted the temp table through Management Studio. If you need more info on the Dedicated Administrator Connection , just search for DAC in Books Online. FYI, you need to put the server in single-user mode to update system catalogs.
March 2, 2007 at 9:09 am
try to run DBCC UPDATEUSAGE
Microsoft recommends running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.
March 2, 2007 at 9:18 am
Ruprecht, you were able to update system tables in 2005 via the DAC ? WOW ! Did you have to do anything special ? It's great to know that MS still left in the DBA's 'special' back door.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply