January 29, 2015 at 10:54 am
select @@VERSION
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
We are using ELMAH to log errors from our web site. It's a very simple db - just 1 table (Elmah_Error), with a clustered PK and a non-clustered IX. If I try to do anything with the table (select, reindex, modify structure) I get:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
Unfortunately this issue dates back prior to our oldest backup (I tried to restore and am getting the same issue).
select object_id('elmah_error')
select * from sys.partitions where object_Id = 549576996
results in:
partion_id object_id index_id
720575940398284805495769960 ...
720575941313167365495769962 ...
720575941313822725495769963 ...
With the missing index_id 1 = clustered index
sp_help elmah gives this for indexes:
idx_ELMAH_TimeUtcnonclustered located on PRIMARYTimeUtc
PK_ELMAH_Errornonclustered, unique, primary key located on PRIMARYErrorId
If I do this:
drop index elmah.PK_ELMAH_Error
or
alter table elmah_error drop CONSTRAINT PK_ELMAH_Error
all yields:
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
If I run dbcc checkdb('elmah') the first time I get this:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
DBCC results for 'ELMAH'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Repair: The page (1:306) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594135576576 (type Unknown).
Repair: The page (1:317) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594135707648 (type Unknown).
Msg 8906, Level 16, State 1, Line 1
Page (1:306) in database ID 8 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
The error has been repaired.
Msg 8906, Level 16, State 1, Line 1
Page (1:317) in database ID 8 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
The error has been repaired.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB fixed 2 allocation errors and 0 consistency errors not associated with any single object.
DBCC results for 'sys.sysrscols'.
There are 885 rows in 13 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 127 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 142 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 18 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 142 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2191 rows in 35 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 721 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 214 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 400 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 219 rows in 18 pages for object "sys.sysobjvalues".
DBCC results for 'sys.syscscolsegments'.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for 'sys.syscsdictionaries'.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for 'sys.sysclsobjs'.
There are 17 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 158 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 134 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysfoqueues'.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftsemanticsdb'.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysftproperties'.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for 'sys.sysxmitbody'.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for 'sys.sysfos'.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for 'sys.sysqnames'.
There are 98 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 3 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 2 allocation errors and 0 consistency errors in database 'ELMAH'.
CHECKDB fixed 2 allocation errors and 0 consistency errors in database 'ELMAH'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
And if I run it again, there are no errors listed:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
DBCC results for 'ELMAH'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 885 rows in 13 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 127 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 142 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 18 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 142 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2191 rows in 35 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 721 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 214 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 400 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 219 rows in 18 pages for object "sys.sysobjvalues".
DBCC results for 'sys.syscscolsegments'.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for 'sys.syscsdictionaries'.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for 'sys.sysclsobjs'.
There are 17 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 158 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 134 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysfoqueues'.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftsemanticsdb'.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysftproperties'.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for 'sys.sysxmitbody'.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for 'sys.sysfos'.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for 'sys.sysqnames'.
There are 98 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 3 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'ELMAH'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
January 29, 2015 at 11:42 am
Drop all the indexes, starting with the nonclustered, then recreate them, starting with clustered. Unless the table itself is damaged, that's likely your best chance to clean this up.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2015 at 12:22 pm
ScottPletcher (1/29/2015)
Drop all the indexes, starting with the nonclustered, then recreate them, starting with clustered. Unless the table itself is damaged, that's likely your best chance to clean this up.
Thanks for response Scott
When I try to drop the non-clustered, I get:
Cannot drop the index 'elmah.idx_ELMAH_TimeUtc', because it does not exist or you do not have permission.
So I did this:
sp_changedbowner (my sql login)
When I try to drop, I get the same result.
In Mgmt Studio, if I bring up dbo under Users, it shows me under the login name. Under Owned Schemas, db_owner has a dot in the checkbox which can't be changed.
If I try it on the clustered I get, essentially the same error:
Cannot drop the index 'elmah.PK_ELMAH_Error', because it does not exist or you do not have permission.
January 29, 2015 at 12:31 pm
Dell Dude (1/29/2015)
ScottPletcher (1/29/2015)
Drop all the indexes, starting with the nonclustered, then recreate them, starting with clustered. Unless the table itself is damaged, that's likely your best chance to clean this up.Thanks for response Scott
When I try to drop the non-clustered, I get:
Cannot drop the index 'elmah.idx_ELMAH_TimeUtc', because it does not exist or you do not have permission.
So I did this:
sp_changedbowner (my sql login)
When I try to drop, I get the same result.
In Mgmt Studio, if I bring up dbo under Users, it shows me under the login name. Under Owned Schemas, db_owner has a dot in the checkbox which can't be changed.
If I try it on the clustered I get, essentially the same error:
Cannot drop the index 'elmah.PK_ELMAH_Error', because it does not exist or you do not have permission.
I don't believe that table has a clustered index; it's a PK, but it's not clustered, which is allowed in SQL Server. It's optimal to have a clus index, but it's not required.
DROP INDEX PK_ELMAH_Error ON elmah_error
DROP INDEX idx_ELMAH_TimeUtc ON elmah_error
If you need to, specify a schema name on the table, although it looks like you're ok without it. For example:
DROP INDEX PK_ELMAH_Error ON elmah.elmah_error
DROP INDEX idx_ELMAH_TimeUtc ON elmah.elmah_error
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2015 at 12:32 pm
Btw, be sure to script out the existing indexes before you delete them so you can recreate them easily ;-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2015 at 12:35 pm
Thanks again Scott, but it's saying both indexes are not present or I don't have permission.
I did sp_changedbowner to my login and get the same result.
January 29, 2015 at 12:39 pm
sp_help elmah gives this for indexes
So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.
DROP INDEX PK_ELMAH_Error ON elmah
DROP INDEX idx_ELMAH_TimeUtc ON elmah
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2015 at 2:16 pm
ScottPletcher (1/29/2015)
sp_help elmah gives this for indexes
So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.
DROP INDEX PK_ELMAH_Error ON elmah
DROP INDEX idx_ELMAH_TimeUtc ON elmah
My bad -- was typing in frustration 🙂
DB = elmah
Table = elmah_error
When trying to drop idx:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131251200 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
When trying to drop px:
Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'elmah_error.PK_ELMAH_Error'. It is being used for PRIMARY KEY constraint enforcement.
January 29, 2015 at 2:22 pm
Dell Dude (1/29/2015)
ScottPletcher (1/29/2015)
sp_help elmah gives this for indexes
So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.
DROP INDEX PK_ELMAH_Error ON elmah
DROP INDEX idx_ELMAH_TimeUtc ON elmah
My bad -- was typing in frustration 🙂
DB = elmah
Table = elmah_error
When trying to drop idx:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131251200 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
When trying to drop px:
Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'elmah_error.PK_ELMAH_Error'. It is being used for PRIMARY KEY constraint enforcement.
Yeah, my bad on the pk, it has to use ALTER:
ALTER TABLE elmah_error DROP CONSTRAINT PK_ELMAH_Error
Run a CHECKTABLE and see if anything bad shows up:
DBCC CHECKTABLE ( elmah_error ) WITH NO_INFOMSGS
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2015 at 2:24 pm
ScottPletcher (1/29/2015)
Dell Dude (1/29/2015)
ScottPletcher (1/29/2015)
sp_help elmah gives this for indexes
So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.
DROP INDEX PK_ELMAH_Error ON elmah
DROP INDEX idx_ELMAH_TimeUtc ON elmah
My bad -- was typing in frustration 🙂
DB = elmah
Table = elmah_error
When trying to drop idx:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131251200 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
When trying to drop px:
Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'elmah_error.PK_ELMAH_Error'. It is being used for PRIMARY KEY constraint enforcement.
Yeah, my bad on the pk, it has to use ALTER:
ALTER TABLE elmah_error DROP CONSTRAINT PK_ELMAH_Error
Run a CHECKTABLE and see if anything bad shows up:
DBCC CHECKTABLE ( elmah_error ) WITH NO_INFOMSGS
ALTER TABLE elmah_error DROP CONSTRAINT PK_ELMAH_Error
This yields my old friend 🙂
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
January 30, 2015 at 1:49 am
If the metadata is corrupt, you're not going to be able to drop the index, no matter what commands you try.
Before trying any more shotgun approaches...
Do you have a backup of this database? Do you have log backups?
What's the downtime and data loss allowances for this database?
How big is that table? (rows/MB)
When was the last time you ran an integrity check that came back clean?
Can you run the following statement and post the full and complete, unedited output?
DBCC CheckCatalog('elmah') with no_infomsgs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2015 at 8:04 am
GilaMonster (1/30/2015)
If the metadata is corrupt, you're not going to be able to drop the index, no matter what commands you try.Before trying any more shotgun approaches...
Do you have a backup of this database? Do you have log backups?
What's the downtime and data loss allowances for this database?
How big is that table? (rows/MB)
When was the last time you ran an integrity check that came back clean?
Can you run the following statement and post the full and complete, unedited output?
DBCC CheckCatalog('elmah') with no_infomsgs
Thanks for the response Mr. Monster.
The full unabridged output is:
- "Command(s) completed successfully."
The sys.partitions says 256 rows, but I don't think that's accurate for rows size on the table. My guestimate (as I can't query the table) is that there's at least 5k rows.
It's not a system critical table by any means. This table has been corrupted for about 3 weeks ago, and our backups are only 10 days old. I've detached and restored from a .bak, and get the same results.
ELMAH is a library to notify of unhandled exceptions in our web apps - we get emails notifications for our website and this db is the logging for these. So if we had to rebuild this db, it's not the best response, but it's an acceptable solution.
The corruption was caused by our mirroring software (double take).
I wanted to take this opportunity to learn more about the inner workings of SQLServer, as I'm primarily a developer and our psuedo-DBA.
**
I just ran: dbcc checktable ('elmah_error') WITH NO_INFOMSGS
And get this:
Msg 602, Level 21, State 30, Line 1
Could not find an entry for table or index with partition ID 72057594131185664 in database 19. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
In Sys.Databases there's no entry for database_id = 19.
January 30, 2015 at 10:43 am
Database 19 will probably be the hidden snapshot that CheckDB creates.
To be honest, I suspect dropping the table will be about the only solution here, although I'd like to see the rest of the output from that CheckDB first as you left out the important lines.
Can you also try to run
DBCC CheckDB('elmah_error') WITH NO_INFOMSGS, Tablock
Make sure there are no connections to the DB from anywhere first.
p.s. It's not 'Mr'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2015 at 3:06 pm
GilaMonster (1/30/2015)
Database 19 will probably be the hidden snapshot that CheckDB creates.To be honest, I suspect dropping the table will be about the only solution here, although I'd like to see the rest of the output from that CheckDB first as you left out the important lines.
Can you also try to run
DBCC CheckDB('elmah_error') WITH NO_INFOMSGS, TablockMake sure there are no connections to the DB from anywhere first.
p.s. It's not 'Mr'
My apologies.
I've been in single user mode during all these tests.
CheckDB on 'elmah' yields:
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
February 2, 2015 at 1:32 am
Dell Dude (1/30/2015)
Msg 608, Level 16, State 1, Line 1No catalog entry found for partition ID 72057594131185664 in database 8. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
Is that absolutely everything that checkDB returns? There should be at least one or two more lines of output.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply