DBCC error & need to delete rows from sys.columns table

Question

DBCC error & need to delete rows from sys.columns table

kittu123

Mr or Mrs. 500

Points: 526
More actions
July 1, 2009 at 1:33 pm

#207847

Getting DBCC error , looks like table got delete & not columns from sys.columns
Check Catalog Msg 3853, State 1: Attribute (object_id=113643698) of row (object_id=113643698,column_id=1) in sys.columns does not have a matching row (object_id=113643698) in sys.objects.
I started the sql server in single user mode from teh command prompt & connected query window using DAC with user as SA (Admin:isntance name) .
ran sp_configure 'allow update', 1
Then when i try
delete syscolumns where id = 113643698
getting the error
Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Let me guess, this is a database upgraded from SQL 2000?

What probably happened is that someone was messing with the system tables in SQL 2000 (where it was allowed) and deleted a row from sysobjects. There's no referential integrity between the system tables and in SQL 2000 running CheckDB did not run checkCatalog.

The bad news: There is no easy way to repair this in SQL 2005. You have two options and neither is nice.

Option 1: Modify the system tables.

To do this you have to start SQL Server in single user mode and then connect with the DAC and enable allow updates. The main problem here is that sysobjects, sys,objects, syscolumns and sys.columns are all views. The real system tables are undocumented and virtually unknown. You'll have to figure out what rows you need to delete from what tables. There's a fair to good chance that you could make things far worse.

Option 2: Recreate the database.

Script all the objects to file. bcp all the data to files. Create a new database, recreate the objects and reload the data.

Option 2's a lot more work but it's far safer. If I had this error on any of my databases, I'd be going for option 2.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kittu123 Mr or Mrs. 500 Points: 526 More actions · Answer 2

Thanks for the response. Your are correct database is restored from sql 2000 to sql server 2005.

I tried 1st option :

•Enable to DAC option from surface configuration

•Sp_configure ‘allow update’ ,1

•Reconfigure

•starting sql server in single user mode . ( -c –m option)

•Connected with DAC and when I try to delete , got the error :

Msg 259, Level 16, State 1, Line 2

Ad hoc updates to system catalogs are not allowed.

Note: I have restored this database on another instance and followed the above steps , I was able to delete.

Second option is may not possible at this time.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Can't see any reason why the delete would fail.

restart SQL in single user mode

Connect via the DAC

Then run sp_configure 'allow updates'

RECONFIGURE

Then find out what tables and what rows you need to delete from and do the deletion.

The second option may take a while, but it's the recommended and far safer way. If you can put up with the error for a while and take the second option when is it a possibility, that's fine. It's not a critical error that absolutely has to be fixed immediately.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95461 More actions · Answer 4

Gail, I'm so glad that you gave such a good response here earlier. We just discovered this on one of our prod dbs, and using option 2 we were able to correct everything. Took a very long day, but got it done.

Edit: we were able to have the two "old" and "new" dbs up at the same time, so instead of bcp we did a select * into newdb.dbo.tbl from olddb.dbo.tbl. The only thing this didn't bring across is the rowguidcol flags.

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2