{"id":1046,"date":"2008-06-12T01:42:00","date_gmt":"2008-06-12T01:42:00","guid":{"rendered":"\/blogs\/paul\/post\/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx"},"modified":"2017-04-13T09:52:01","modified_gmt":"2017-04-13T16:52:01","slug":"using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables\/","title":{"rendered":"Using the Dedicated Admin Connection to fix Msg 8992: corrupt system tables"},"content":{"rendered":"

<\/p>\n

\nToday I presented my brand new session Surviving Corruption: From Detection to Recovery<\/em> at TechEd. I had a lot of fun putting together the demos, presenting the session, and talking to people afterwards. During the session, I promised to blog each of the demos so that everyone can run through them – here's the first one.\n<\/p>\n

\nOn SQL 2000, it was pretty easy to get into the system tables and manually change them – all you had to do was:\n<\/p>\n

\n

\n\tEXEC <\/font>sp_configure <\/font>'allow updates'<\/font>,<\/font> 1<\/font>;
\n\t<\/font>GO
\n\tRECONFIGURE <\/font>WITH <\/font>OVERRIDE<\/font>;
\n\t<\/font>GO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd then you could insert, update, and delete whatever you wanted in the all the system tables, including the critical three – sysindexes, sysobjects, and syscolumns. The problem was that sometimes people actually did this and messed things up – for instance, by manually deleting an object from sysobjects, but leaving around all the other info about the object – such as indexes and columns. DBCC CHECKCATALOG<\/font> in SQL 2000 would find this, but DBCC CHECKDB<\/font> would not – as it didn't run the DBCC CHECKCATALOG<\/font> code – any most people do not run DBCC CHECKCATALOG<\/font> at all. Many times now, I've seen databases upgraded to 2005 and suddenly DBCC CHECKDB<\/font> is reporting metadata corruption errors – all because someone had manually changed the system tables on 2000, and I changed DBCC CHECKDB<\/font> in 2005 to include<\/em> the DBCC CHECKCATALOG<\/font> checks.\n<\/p>\n

\nThis demo is all about that. I created a 2000 database, manually deleted a row in sysobjects and then upgraded the database to 2005. The corrupt database is available in a zip file – DemoCorruptMetadata.zip<\/a>. If you unzip it into a folder C:\\SQLskills then you can attach it using:\n<\/p>\n

\n\tRESTORE DATABASE<\/font> DemoCorruptMetadata <\/font>FROM DISK <\/font>= <\/font>'C:\\SQLskills\\DemoCorruptMetadata.bak'
\n\t   <\/font>WITH MOVE <\/font>'DemoCorruptMetadata' <\/font>TO <\/font>'C:\\SQLskills\\DemoCorruptMetadata.mdf'<\/font>,
\n\t   <\/font>MOVE <\/font>'DemoCorruptMetadata_log' <\/font>TO <\/font>'C:\\SQLskills\\DemoCorruptMetadata_log.ldf'<\/font>,
\n\t   <\/font>REPLACE<\/font>;
\n\t<\/font>GO<\/font><\/font><\/p>\n

\n\tSo what does the corruption look like on 2005?<\/font>\n\t<\/p>\n

\n\t\t<\/p>\n

\n\t\tDBCC CHECKDB <\/font>(<\/font>DemoCorruptMetadata<\/font>) <\/font>WITH NO_INFOMSGS<\/font>,<\/font> ALL_ERRORMSGS<\/font>;
\n\t\t<\/font>GO<\/font>
\n\t\tMsg 8992, Level 16, State 1, Line 1
\n\t\tCheck Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
\n\t\tMsg 8992, Level 16, State 1, Line 1
\n\t\tCheck Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
\n\t\t<\/font>CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
\n\t\tCHECKDB found 0 allocation errors and 2 consistency errors in database 'DemoCorruptMetadata'.<\/font>\n\t\t<\/p>\n

\t\t<\/font>\n\t<\/p><\/blockquote>\n

\n\tThis is what we expect. Notice that there's no recommended repair level at the end of the output – this is because CHECKDB can't repair metadata corruptions. We can't fix this with a backup – unless we have a backup from 2000 from before the manual delete in the system tables. To fix this we'd need to go back to 2000, fix the corruption, and then upgrade again – usually not feasible.<\/font>\n\t<\/p>\n

\n\tInstead, we're going to fix it by manually altering the system tables in 2005 – something that's purportedly not possible. First let's see what tables there are that could include column information (remembering that the system catalogs were completely rewritten between 2000 and 2005):<\/font>\n\t<\/p>\n

\n\t\t<\/p>\n

\n\t\tSELECT [name] <\/font>FROM<\/font> DemoCorruptMetadata<\/font>.<\/font>sys.objects <\/font>WHERE <\/font>[name] <\/font>LIKE <\/font>'%col%'<\/font>;
\n\t\t<\/font>GO<\/font>\n\t\t<\/p>\n

\t\t<\/font><\/p>\n

\n\t\tname
\n\t\t——————
\n\t\tsysrowsetcolumns
\n\t\tsyshobtcolumns
\n\t\tsyscolpars
\n\t\tsysiscols<\/font>\n\t\t<\/p>\n<\/blockquote>\n

\n\tI know that sysrowsetcolumns and syshobtcolumns are involved at low-levels of the Storage Engine and don't contain relational metadata, so let's try syscolpars<\/font>. I want to see what columns there are to see if one of the looks like an object ID, and another looks like a column ID. This query will just return the table columns, with no rows (because the condition 1=0 is always false:<\/font>\n\t<\/p>\n

\n\t\t<\/p>\n

\n\t\tSELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> DemoCorruptMetadata<\/font>.<\/font>sys.syscolpars <\/font>WHERE<\/font> 1 <\/font>=<\/font> 0<\/font>;
\n\t\t<\/font>GO<\/font><\/font>\n\t\t<\/p>\n

\t\t<\/font><\/p>\n

\n\t\tMsg 208, Level 16, State 1, Line 1
\n\t\t<\/font>Invalid object name 'DemoCorruptMetadata.sys.syscolpars'.<\/font>\n\t\t<\/p>\n

\t\t<\/font><\/font>\n\t<\/p><\/blockquote>\n

\n\tI can't bind to internal system tables in 2005. But – I can bind to internal system tables using the Dedicated Admind Connection (or DAC for short). This is documented in Books Online at http:\/\/msdn.microsoft.com\/en-us\/library\/ms179503.aspx<\/a>. You can get to the DAC through SQLCMD using the \/A switch. So – assuming I'm now connected through the DAC, I'll try that command again:<\/font>\n\t<\/p>\n

\n

\n\t\tC:\\Documents and Settings\\paul>sqlcmd \/A
\n\t\t1> USE DemoCorruptMetadata;
\n\t\t2> GO
\n\t\tChanged database context to 'DemoCorruptMetadata'.
\n\t\t1> SELECT * FROM sys.syscolpars WHERE 1=0;
\n\t\t2> GO
\n\t\tid          number colid       name<\/font>\n\t\t<\/p>\n

\n\t\txtype utype       length prec scale collationid status      maxinrow xmlns
\n\t\t dflt        chk         idtval<\/font>\n\t\t<\/p>\n

\n\t\t———– —— ———– ————————————————-
\n\t\t——————————————————————————-
\n\t\t—– ———– —— —- —– ———– ———– ——– ———–
\n\t\t ———– ———– ——————————————————-
\n\t\t———–<\/font>\n\t\t<\/p>\n

\n\t\t(0 rows affected)
\n\t\t<\/font>1><\/font>\n\t\t<\/p>\n<\/blockquote>\n

\n\tThis looks like the table. Now I'll query against it using the object ID from the original corruption message:<\/font>\n\t<\/p>\n

\n

\n\t\t1> SELECT colid, name FROM sys.syscolpars WHERE id = 1977058079;
\n\t\t2> GO
\n\t\tcolid       name
\n\t\t<\/font>———– ——————————————————————–
\n\t\t————————————————————
\n\t\t          1 SalesID
\n\t\t          <\/font>2 CustomerID<\/font>
\n\t\t(2 rows affected)
\n\t\t1><\/font>\n\t\t<\/p>\n<\/blockquote>\n

\n\tCool. So I'll try deleting the orphaned columns:<\/font>\n\t<\/p>\n

\n

\n\t\t1> DELETE FROM sys.syscolpars WHERE id = 1977058079;
\n\t\t2> GO
\n\t\tMsg 259, Level 16, State 1, Server ROADRUNNERPR, Line 1
\n\t\tAd hoc updates to system catalogs are not allowed.
\n\t\t1><\/font>\n\t\t<\/p>\n<\/blockquote>\n

\n\tHmm. And it doesn't help if I set 'allow updates' to 1, or try putting the database into single-user mode.<\/font>\n\t<\/p>\n

\n\tThere IS a way though. You can put the SERVER into single-user mode, then connect with the DAC and you can then update the system tables. This particular twist on using the DAC isn't documented anywhere except in an MSDN forum thread answered by someone from Microsoft (see <\/font><\/font>here<\/font><\/a>).<\/font><\/font>\n\t<\/p>\n

\n\tBEWARE<\/font><\/em><\/u><\/strong> (if I could put little flashing lights around this too then I would…) that this is undocumented and unsupported – misuse will lead to unrepairable corruption of your databases.<\/font><\/font>\n\t<\/p>\n

\tThe sequence of events to follow is:<\/font> <\/p>\n