1

CHECKCATALOG is failing on a database that originated in SQL Server 2000. I am getting errors of the type:

Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=489156888) of row (object_id=489156888,column_id=1) in sys.columns does not have a matching row (object_id=489156888) in sys.objects.

There are 146 columns in sys.columns that do not exist in sys.objects. This represents 21 tables. If I could easily delete these rows from the sys.columns view I would (blasphemy I know). CHECKDB had not been ran before so these errors went undetected and all the backups will contain the same error.

My plan is to export the existing tables into a new database. But the db is 170GB so this is not trivial.

My question is this: can I just reuse my existing .mdf/.ldf files? Since the system tables are stored in the master database, I could create a [new_work] database and attach the .mdf/.ldf files from the [orig_work] database. Then when I drop the corrupt [orig_work] db the bad entries would be deleted from the system table.

Would this work to fix my error?

  • If you have a good backup(with clean checkdb ran), try restoring the database to a new server instance. If not then your only option as per this KB is to bcp out and bulk insert in a new database. Alternatively, if you still have the database on sql 2000, then you can update the system tables and delete the columns DELETE FROM syscolumns WHERE objectid = 489156888 and then run dbcc checkcatalog (dbname) and then reupgrade the database by restoring on sql server 2005 instance. – Kin Shah Aug 13 '14 at 19:44
  • This isn't going to help solve the issue, but it may help future users from running into the same issue: how exactly did you move the database from 2000 -> 2005? – Aaron Bertrand Aug 13 '14 at 19:47
  • Also what you propose won't work. You can't "reuse" MDF/LDF files. You can create a new database, and have it auto-expand to the size of the current files by specifying the file names and sizes explicitly, and with instant file initialization this will not be too painful for the data file at least (and you may consider starting off with a smaller log file to avoid this up-front cost). But then you'd have to move the objects and data (and some tools for doing this might not work so well if the metadata is hosed). – Aaron Bertrand Aug 13 '14 at 19:49
  • Thanks for the feedback. I will proceed with exporting the tables into a new database. – Andy Aug 13 '14 at 20:00
  • You should be able to connect to the database using the DAC and delete the extra rows from sys.columns. – mrdenny Aug 14 '14 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.