SQL 2005 - Check Catalog Msg 3853, State 1: error

[rileypetty]

Hello, I need some advice on a MS SQL 2005 problem. Our customer had their POS application and database hosted by a vendor that informed them two months ago that they were no longer hosting and the customer would have host the application and the DB locally. We were contracted to make the conversion and did.

We built the new server, Win Server 2003, SQL 2005 and the POS application. The vendor remotely downloaded and installed the DB using the restore process. The customer needed to get productive immediately so we got everything working and it’s been going fine for the past two weeks. When I finally got around to running a DB integrity check it failed.

Check Catalog Msg 3853, State 1: Attribute (there were ten (10) errors all total)

Lots of researched indicate this error is related to a dependencies issue regarding a user table and the system database. This DB was once a SQL 2000 version and access to the system DB was allowed then but not in SQL 2005. Also, SQL 2005 DB integrity check is more sensitive and flags these types of errors where SQL 2000 did not. I think I’m right.

Based on a suggestion from a SQL forum, I loaded the DB on a test machine, did some record counts on the four tables involved and exported the four tables to a 2nd test DB (empty). Then went back to first DB and deleted the four tables. Then imported the four tables back to the first DB and ran record counts. Everything looked ok and then ran the DB integrity check again. No errors. Everything seems to be working fine in the test environment. I don’t have access to the application to do the real test but plan on that soon.

My question is, am I ok? Am I going in the right direction? Am I missing anything?
Thanks for any information you might be able to give me.

 

[DrSql]

Did you run the SQL Server 2005 upgrade avisor against SQL 2000 database prior to the move else I would suggest to running that and analyse the report.

You can obtain the copy

http://www.microsoft.com/downloads/...6B-7E05-4322-A677-95AB44F12D75&displaylang=en

Dr.Sql
Good Luck.

 

[rileypetty]

Thanks DrSql for the response. It's our (small company hired to make local conversion) understanding the DB was converted from 2000 to 2005 several years ago by the previous vendor doing the hosting. We got involved back in October and everything we've been working with is 2005. I've tried to get the previous vendor involved but cannot get them to return calls and emails. We have a copy of the originally download DB and that's the one I used for testing (the errors were already there). The errors don't appear to be so serious that they cause problems but we would like to clear them up and the testing appears to do that. I've also run Index and Shrink jobs with no problems. Only DBCC CHECKDB returns errors. Thanks again.

 

[DrSql]

At this time it will be trial and error option and here is an article that talks in details about your error...

http://www.sqlservercentral.com/articles/65804/

Corruption in the Metadata
Msg 3853, Level 16, State 1, Line 1 Attribute (object_id=181575685) of row (object_id=181575685,column_id=1) in sys.columns does not have a matching row (object_id=181575685) in sys.objects.
This type of error usually appears in a database upgraded from SQL 2000, where someone did direct updates to the system tables.

There are no foreign keys enforced among the system tables in any version of SQL, so it was possible on SQL 2000 to delete a row from sysobjects (for example a table) and leave the rows in syscolumns and sysindexes that reference the deleted row.

On SQL 2000, CheckDB did not do a check of the system catalog, so this kind of problem often went completely unnoticed. On SQL 2005, CheckDB does do consistency checks of the system catalog, and so these errors can appear.

Fixing these is not trivial. CheckDB will not repair them, as the only fix is to delete records from the system tables, which may cause major data loss. If there's a backup of the database from before it was upgraded to SQL 2005 and the upgrade was very recent, then that backup can be restored to SQL 2000, the system tables fixed on SQL 2000 and then the database upgraded again.

If there is no SQL 2000 backup, or the upgrade was too long ago and the data loss is unacceptable, then there are two possible fixes. First, edit the system tables in SQL 2005, which is a complex and very risky process, as the system tables are not documented and are much more complex than they were on previous versions. See this blog post for details -

http://www.sqlskills.com/BLOGS/PAUL...on-to-fix-Msg-8992-corrupt-system-tables.aspx

The other solution is to generate scripts of all the objects in the database and export all of the data. Create a new database, recreate the objects and reload the data.

The second option is usually the recommended one.

Irreparable Corruption
CheckDB can't repair everything. Any errors like these are irreparable and the only way to resolve them is to restore a backup of the database that does not have the corruption. If there is a full, unbroken log chain from that backup up until the current time, then the log tail can be backed up and the database can be restored without any data loss.

If there is no clean backup, then the only remaining option is to generate scripts of the database objects and export the data that is accessible. It is quite likely, due to the corruption, that not all of the data will be accessible, and most likely not all of the objects will script without error.


Good luck.

Dr.Sql
Good Luck.

 

[rileypetty]

Thanks again DrSql for the response, very informative and helpful. While I’m certainly not going to ignore these errors, I get the impression that if it takes me a while, like a couple of weeks (customer schedule) to fix them that may be ok. I can’t verify when the migration to 2005 occurred but it appears like it’s been several years. As I said before, I can make the errors go away on the test machine by exporting the four tables involved from test DB #1 to test DB #2. Then I drop the four tables out of test DB #1 and import the four tables from test DB #2 back to test DB #1. After the importing I run a DBCC CheckDB(test DB #1) and get no errors. All ten errors disappear. Also, when I do all this exporting and importing I do record count checks each step of the way with the numbers always the same so it does not appear like there is any data loss.

Are you telling me I need to do the whole DB, not just the four tables?

 

[rileypetty]

This is a follow-up to our SQL 2005 database integrity problems. We were finally able to communicate with the vendor that made the original SQL 2000 to SQL 2005 conversion. It turns out there was a Stored Procedure that was no longer used that was causing the errors. All we had to do was delete (according to the vendor) the SP and once we did the errors went away and everything is working fine. While our Export/Import and table delete plan appeared to work in our test environment I’m glad we didn’t execute such a complicated solution.