Today I presented my brand new session Surviving Corruption: From Detection to Recovery 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.
On SQL 2000, it was pretty easy to get into the system tables and manually change them – all you had to do was:
EXEC sp_configure 'allow updates', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
And 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 in SQL 2000 would find this, but DBCC CHECKDB would not – as it didn't run the DBCC CHECKCATALOG code – any most people do not run DBCC CHECKCATALOG at all. Many times now, I've seen databases upgraded to 2005 and suddenly DBCC CHECKDB is reporting metadata corruption errors – all because someone had manually changed the system tables on 2000, and I changed DBCC CHECKDB in 2005 to include the DBCC CHECKCATALOG checks.
This 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. If you unzip it into a folder C:\SQLskills then you can attach it using:
RESTORE DATABASE DemoCorruptMetadata FROM DISK = 'C:\SQLskills\DemoCorruptMetadata.bak'
WITH MOVE 'DemoCorruptMetadata' TO 'C:\SQLskills\DemoCorruptMetadata.mdf',
MOVE 'DemoCorruptMetadata_log' TO 'C:\SQLskills\DemoCorruptMetadata_log.ldf',
REPLACE;
GOSo what does the corruption look like on 2005?
DBCC CHECKDB (DemoCorruptMetadata) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Msg 8992, Level 16, State 1, Line 1
Check 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.
Msg 8992, Level 16, State 1, Line 1
Check 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.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'DemoCorruptMetadata'.
This 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.
Instead, 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):
SELECT [name] FROM DemoCorruptMetadata.sys.objects WHERE [name] LIKE '%col%';
GO
name
——————
sysrowsetcolumns
syshobtcolumns
syscolpars
sysiscolsI 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. 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:
SELECT * FROM DemoCorruptMetadata.sys.syscolpars WHERE 1 = 0;
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DemoCorruptMetadata.sys.syscolpars'.
I 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. 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:
C:\Documents and Settings\paul>sqlcmd /A
1> USE DemoCorruptMetadata;
2> GO
Changed database context to 'DemoCorruptMetadata'.
1> SELECT * FROM sys.syscolpars WHERE 1=0;
2> GO
id number colid namextype utype length prec scale collationid status maxinrow xmlns
dflt chk idtval———– —— ———– ————————————————-
——————————————————————————-
—– ———– —— —- —– ———– ———– ——– ———–
———– ———– ——————————————————-
———–(0 rows affected)
1>This looks like the table. Now I'll query against it using the object ID from the original corruption message:
1> SELECT colid, name FROM sys.syscolpars WHERE id = 1977058079;
2> GO
colid name
———– ——————————————————————–
————————————————————
1 SalesID
2 CustomerID
(2 rows affected)
1>Cool. So I'll try deleting the orphaned columns:
1> DELETE FROM sys.syscolpars WHERE id = 1977058079;
2> GO
Msg 259, Level 16, State 1, Server ROADRUNNERPR, Line 1
Ad hoc updates to system catalogs are not allowed.
1>Hmm. And it doesn't help if I set 'allow updates' to 1, or try putting the database into single-user mode.
There 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 here).
BEWARE (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.
The sequence of events to follow is:
- make a backup of the database just in case something goes wrong
- shutdown the server
- go to the binaries directory (e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) and start the server in single-user mode using 'sqlservr -m'
- connect back in using SQLCMD /A, and run the deleta again. This time it will work, but will give an error about metadata cache consistency:
C:\Documents and Settings\paul>sqlcmd /A
1> USE DemoCorruptMetadata;
2> GO
Changed database context to 'DemoCorruptMetadata'.
1> DELETE FROM sys.syscolpars WHERE id = 1977058079;
2> GO(2 rows affected)
Warning: System table ID 41 has been updated directly in database ID 12 and cache coherence may not have been maintained. SQL Server should be restarted.
1>
- The system table has been updated, but the in-memory cache of metadata is now out-of-sync with the system tables. So, shutdown the server again as the message suggests and restart it normally
- run CHECKDB again and you'll see the corruption has been fixed.
Hope this helps some of you. Watch this space for the next demo from TechEd of repairing corruption when no backup is available.
24 thoughts on “Using the Dedicated Admin Connection to fix Msg 8992: corrupt system tables”
Thanks for sharing this helpful demo, looking for the next one.
Have a nice weekend.
Paul,
at a very poor moment in a SQL 2005 upgrade this weekend – we hit exactly the issue you described above.
Thanks very much for an extremely well-timed article :)
Paul,
Your blogs have be a great help to me thanks!
With more than 67,000 errors on our ERP database, your posting here was a life saver!
SQLCMD allows the use of an input file, so I set up a SQL Server Job:
[b]Step 1: ALTER DATABASE <CorruptDB> SET SINGLE_USER[/b]
[b]Step 2: DBCC CHECKDB (‘<CorruptDB>’, REPAIR_FAST) WITH ALL_ERRORMSGS[/b]
I had this Step output its results to a text file on the server.
[b]Step 3: ALTER DATABASE <CorruptDB> SET MULTI_USER[/b]
Once the file was generated, I used some regex Find/Replace to strip down the file to the bad reference IDs only. In my case, I had many IDs repeated multiple times, so I copied my list into Excel for a quick sort and deleted the "duplicate" rows.
Another regex to build an input file formatted like this:
[b]USE <CorruptDB>;
GO
DELETE FROM sys.syscolpars WHERE id = 328107;
GO
DELETE FROM sys.syscolpars WHERE id = 380162;
GO
DELETE FROM sys.syscolpars WHERE id = 432217;
GO
[/b]
Etc…
Saved my input file as D:\integrep.01 on the server, stopped all the SQL services, then ran the following:
[b]sqlservr -m[/b] (In one cmd window)
[b]sqlcmd /A -i D:\integrep.01[/b] (In a separate cmd window)
It might be worth noting that with the number of integrity errors I had, for some reason the dump would only spit out 32,763 records at a time. That number seems really arbitrary to me, but the first dump was exactly that many records, as was the second. The third dump finished off the remainder. So I repeated the above command three times, though I’m sure this would have worked just as well:
[b]sqlcmd /A -i D:\integrep.01,D:\integrep.02,D:\itegrep.03[/b]
The problem with that method is that, unless you have a duplicate database to play with, you’re only going to be able to repair one batch at a time – unless that 32,763 record limitation was some sort of fluke on just my server.
Thank you so much for posting this. I just wanted to share my experience following your suggestion here in case someone has integrity errors on as grand a scale as I had.
Unfortunately I have run across the same corruption in 4 databases. I just started a job this last week and it seems they have been living with this for a while, I think no maintenance has ever been done to the Databases so far. The question is will this work without a backup of the uncorrupted database. From your example it looks to be that no data from the original uncorrupted database was needed. What are the possible implications of continuing without the corrupted data once deleted? Sorry I am a novice and don’t entirely understand.
Hmm – I would recommend not doing this if you’re a novice (from your own admission). You’re not deleting data with this procedure – you’re deleting unmatched entries from system tables – so there shouldn’t be a problem. Be careful.
ok I have deleted the rows in question but I have one more error remaining…
"Attribute (parent_object_id=830235054) of row (object_id=846235111) in sys.objects does not have a matching row (object_id=830235054) in sys.objects."
Do i use the same method to delete that one?
Yup – that should work.
Using the same theory I did:
SELECT [name] FROM EVEREST_RMA.sys.objects WHERE [name] LIKE ‘%obj%’;
GO
and of the 23 results the ones containing sys in the beginning are:
sysbinobjs
sysbinsubobjs
sysclsobjs
sysmultiobjrefs
sysnsobjs
sysobjkeycrypts
sysobjvalues
sysschobjs
syssingleobjrefs
systypedsubobjs
Any idea which one I should take a look at to find the one containing the data and the column name to search by?
This is where it gets more dangerous – when the errors start to move away from the example. Don’t know which one of these to look in – my guess would be you’re looking for an ‘id’ column and something with a ‘parent’
This is what I did (SQL 2005 build 9.00.3073.00)(ran in single user mode and admin connection for most of these):
0)create a copy of the "corrupted" database from a backup
1)ran dbcc checkcatalog;
2)here is a partial output:
Attribute (parent_object_id=948250483) of row (object_id=964250540) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=980250597) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=996250654) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1012250711) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1028250768) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1044250825) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1060250882) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1076250939) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1092250996) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1108251053) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1124251110) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1140251167) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1156251224) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
Attribute (parent_object_id=948250483) of row (object_id=1172251281) in sys.objects does not have a matching row (object_id=948250483) in sys.objects.
3)investigated the sys tables, for example:
select name from GregsDB.sys.objects where name like ‘%obj%’ and type_desc = ‘system_table’
4)confirmed that sys.sysschobjs table indeed has id and pid values listed above (e.g. id = 964250540 and pid = 948250483)
5)delete those rows
delete sys.sysschobjs where id = 964250540 and pid = 948250483
delete sys.sysschobjs where id = 980250597 and pid = 948250483
delete sys.sysschobjs where id = 996250654 and pid = 948250483
delete sys.sysschobjs where id = 1012250711 and pid = 948250483
delete sys.sysschobjs where id = 1028250768 and pid = 948250483
delete sys.sysschobjs where id = 1044250825 and pid = 948250483
delete sys.sysschobjs where id = 1060250882 and pid = 948250483
delete sys.sysschobjs where id = 1076250939 and pid = 948250483
delete sys.sysschobjs where id = 1092250996 and pid = 948250483
delete sys.sysschobjs where id = 1108251053 and pid = 948250483
delete sys.sysschobjs where id = 1124251110 and pid = 948250483
delete sys.sysschobjs where id = 1140251167 and pid = 948250483
delete sys.sysschobjs where id = 1156251224 and pid = 948250483
delete sys.sysschobjs where id = 1172251281 and pid = 948250483
6)warnings are expected, like this one:
Warning: System table ID 34 has been updated directly in database ID 6 and cache coherence may not have been maintained. SQL Server should be restarted.
(1 row(s) affected)
7)then dbcc checkcatalog produced different set of errors:
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:1910) in object ID 0, index ID -1, partition ID 0, alloc unit ID 81018978893824 (type Unknown), but it was not detected in the scan.
8)ran dbcc checkdb with REPAIR_ALLOW_DATA_LOSS option
9) got these:
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:1743) in object ID 0, index ID -1, partition ID 0, alloc unit ID 938026865852416 (type Unknown), but it was not detected in the scan.
The error has been repaired.
10) after this point, dbcc checkcatalog produced clean results
11)ran RedGate SQL compare against the original "corrupted" database and the copy that the steps above were applied.
100% match.
Added:
the dangling rows in the sys.sysschobjs where leftover from some old bad merge replication removed poorly long ago; in my case I also verified that the name values in sys.sysschobjs did contain %merge%;
this was an additional confirmation that deletion of the rows was safe.
Hello,
I’ve same issue in my old SQL 2005 database. When i try to run below command:
DELETE FROM sys.sysschobjs WHERE ID=486456348
It throws fatal error “* CPerIndexMetaQS::ErrorAbort – Index corruption” and could not proceed further. Any help would be appreciated.
Then your database has real corruption in it. Restore from your backups or export as much as possible into a new database. You’re not going to be able to fix it.
I have this error with sys.syscolpars table (SQL Server 2008 R2), however, when I query it for the id provided in the error message from DBCC CHECKDB, I get nothing back.
I also ran DBCC CHECKCATALOG and received the following messages:
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=680748523) of row (object_id=680748523,index_id=1) in sys.indexes does not have a matching row (object_id=680748523) in sys.objects.
Msg 3855, Level 16, State 1, Line 1
Attribute (data_space_id=1) exists without a row (object_id=680748523,index_id=1) in sys.indexes.
Msg 3855, Level 16, State 1, Line 1
Attribute (lob_data_space_id=1) exists without a row (object_id=680748523,index_id=1) in sys.indexes.
I’m struggling to find any information about these – what is wrong and how do I fix it?
Looks like (unless you have a backup to restore to) you need to delete the row from sys.sysidxstats with [id] = 680748523. Disclaimer: you do so entirely at your own risk, and make sure to take a copy of the database beforehand in case something goes wrong.
Hi!
I have SQL Server 2005 (9.0.4035)
When I try to browse Procedures I get the message:
Possible schema corruption. Run DBCC CHECKCATALOG.
…
MS SQL Server, Error:211
If I Run it, I got:
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=2) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=3) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=4) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=5) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=6) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=8) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=9) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=10) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=11) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=12) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=13) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=14) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=15) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=16) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=17) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=18) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=19) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=20) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=21) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=22) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=23) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=24) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=25) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=26) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=28) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=29) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=30) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=31) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=32) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=33) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=34) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=35) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=36) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=37) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=38) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=39) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=40) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=41) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=656894481) of row (class=0,object_id=656894481,column_id=0,referenced_major_id=1298337377,referenced_minor_id=42) in sys.sql_dependencies does not have a matching row (object_id=656894481) in sys.objects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Can I decide this problem?
Yup – you can fix it in the same way by deleting the rows from the underlying system table for sys.sql_dependencies. Be careful, do this at your own risk as you may end up causing more corruption if you make a mistake.
Paul – adding my appreciation here. I had a series of clients DBs on a SQL 2016 cluster all restored from same master that had some fragments of full-text indexing from a deep past still present. After a power failure, the recovery process seems to have found and been unhappy with them – access of the long-ago FT indexed tables was failing over the cluster regularly and gave:
‘Corruption in database ID 6, object ID 25 possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.’
— instance was NOT Full_text enabled
— no entry in sys.dm_fts_active_catalogs
— entries in sys.fulltext_index_catalog_usages
— entries in sys.fulltext_index_columns
using your instructions above to gain a DAC connection to SQl in single user mode I was able to execute:
delete from sys.fulltext_index_columns
go
delete from sys.syssingleobjrefs where depid in (select object_id from sys.fulltext_index_catalog_usages)
go
Excellent – glad it worked!
Msg 8992, Level 16, State 1, Line 27
Check Catalog Msg 3853, State 1: Attribute (object_id=871010184) of row (object_id=871010184,column_id=1) in sys.columns does not have a matching row (object_id=871010184) in sys.objects.
Msg 8992, Level 16, State 1, Line 27
Check Catalog Msg 3853, State 1: Attribute (object_id=871010184) of row (object_id=871010184,column_id=2) in sys.columns does not have a matching row (object_id=871010184) in sys.objects.
Msg 8992, Level 16, State 1, Line 27
Check Catalog Msg 3853, State 1: Attribute (object_id=871010184) of row (object_id=871010184,column_id=3) in sys.columns does not have a matching row (object_id=871010184) in sys.objects.
Msg 2576, Level 16, State 1, Line 27
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:7816) in object ID 0, index ID -1, partition ID 0, alloc unit ID 338557500129280 (type Unknown), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 3 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 3 consistency errors in database XXXXX
Hey paul, We are facing issues with Check Db with above error. We are not able to get the Object which is associated with the number.
Please help me to get the solution.
Thanks
DAN
You’ll need to fix the metadata errors manually as I describe in the post, and then run repair to fix the IAM issue (assuming you have no backups for some reason).
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Executing the query “DBCC CHECKDB(N’BBB’) WITH NO_INFOMSGS
” failed with the following error: “Check Catalog Msg 3853, State 1: Attribute (principal_id=47) of row (stoplist_id=5) in sys.fulltext_stoplists does not have a matching row (principal_id=47) in sys.database_principals.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database ‘BBB’.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Above errors are seen in checkdb, will the same procedure apply
SELECT * FROM BBB.sys.fulltext_stoplists WHERE [principal_id] = 47
GO
stoplist_id name create_date modify_date principal_id
5 CustomStopword 2015-05-26 00:47:53.977 2019-11-29 01:24:14.793 47
SELECT * FROM BBB.sys.database_principals WHERE [principal_id] = 47
no result
I’ll tentatively say yes, as I’ve never messed with those tables. But it does seem like deleting (stoplist_id = 5 and principal_id = 47) should work to remove that error. This is at your own risk – be careful.