October 10, 2010 at 7:24 pm
I have a large database which we are about to move to SQL Server 2008. Up until this point, it has not been under my control.
When running a CHECKDB I get a small number (<100) 8992 errors. From what I can piece together, when this database resided on SQL Server 2000, the then DBA loved tinkering with system tables. Long story short, he deleted a table from sysobjects directly, leaving orphaned column references in syscolumns. Column references still exist for a table that is long gone.
I know the official Microsoft approach on this is to script out the DB and port all data over into the new database. For a number of reasons, this is not practical for this database.
I am wondering if anyone has used the DAC to mod the system tables in 2005 or 2008 in order to fix this issues? References can be found at:
http://www.sqlservercentral.com/articles/Corruption/69382/
I have tested it and it seems to work fine. Add given the fact that I am just removing column references for tables that no longer exist, I personally consider this very low risk.
Again, it is not the MS recommended fix, but I think it will be a LONG time before we see MS recommend modifying system tables even if it is a simple fix. I seem to recall them not even talking about registry changes at the OS level for quite some time.
Thoughts? Comments? Experiences?
October 10, 2010 at 9:21 pm
Well, you quoted my article. And that is how I fixed exactly those same issues... except I had thousands instead of hundreds.
There's only two ways to fix the problem. Follow the guidelines in my article/Paul's blog, or do it the MS way and script/import everything into a new database.
HOWEVER, you said:
I have a large database which we are about to move to SQL Server 2008.
It's a LOT easier to fix this in 2000, before upgrading to 2008, than it is to fix it in 2008. Since you can hit the system tables directly, it would be easier that way. However, in either case, ensure you have a good backup, and have tested that you can restore from it, before doing it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 10, 2010 at 9:34 pm
WayneS (10/10/2010)
Well, you quoted my article. And that is how I fixed exactly those same issues... except I had thousands instead of hundreds.There's only two ways to fix the problem. Follow the guidelines in my article/Paul's blog, or do it the MS way and script/import everything into a new database.
HOWEVER, you said:
I have a large database which we are about to move to SQL Server 2008.
It's a LOT easier to fix this in 2000, before upgrading to 2008, than it is to fix it in 2008. Since you can hit the system tables directly, it would be easier that way. However, in either case, ensure you have a good backup, and have tested that you can restore from it, before doing it.
Hi Wayne! And a great article it is.
I should clarify the whole moving to SQL Server 2008 part. We are moving it from SQL Server 2005. The SQL 2000 days are long behind us for this database.
Luckily, this is all in testing right now and we will have ample opportunity to make sure it does not cause any issues, although unless I screw something up, I cannot see where it would.
I am confident this is the path we are going to take. There is just something wrong with leaving this corruption in place. If nothing else, it means I need to go over every DBCC CHEKDB with a fine toothed comb to make sure there are no new issues.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply