sergiisyrovatchenko/SQLIndexManager

Не могу работать с другими базами, если одна из них битая

Anagramma opened this issue · 7 comments

Имеются несколько подключенных баз. Одна имеет ошибки. Я хочу оптимизировать другую(ие)
При подключении к такому серверу получаю ошибку и дальше работать не могу:
img-2021-06-18-14-04-43

Hi @Anagramma

Please run this:

DBCC CHECKDB ([barabsport])
DBCC CHECKDB ([master])

SELECT * FROM sys.databases WHERE name = 'barabsport'

and provide the outputs. Will try reproduce this issue from my side.

Thanks for your feedback!

Hi @Anagramma

Please run this:

DBCC CHECKDB ([barabsport])
DBCC CHECKDB ([master])

SELECT * FROM sys.databases WHERE name = 'barabsport'

and provide the outputs. Will try reproduce this issue from my side.

Thanks for your feedback!

Спасибо за быструю реакцию. Я знаю, что в базе ошибки, просто хочу работать с другими базами т.е. я не вижу списка баз в таком случае (надеюсь что понятно выражаюсь).

Вот результат вывода:
`Сообщение 7985, уровень 16, состояние 2, строка 1
System table pre-checks: Object ID 3. Could not read and latch page (1:117264) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'barabsport'.
Сообщение 5233, уровень 16, состояние 98, строка 1
Table error: alloc unit ID 7791770447363571712, page (42632:-1054618961). The test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. The values are 12716041 and -10.
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 'barabsport'.
DBCC results for 'master'.
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 822 rows in 11 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 116 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 128 rows in 3 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.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 8 rows in 1 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.syspru'.
There are 8 rows in 1 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 16 rows in 2 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 16 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 8 rows in 1 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 1952 rows in 15 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 74 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syslogshippers'.
There are 0 rows in 0 pages for object "sys.syslogshippers".
DBCC results for 'sys.syscolpars'.
There are 659 rows in 14 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 34 rows in 1 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 1 rows in 1 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 7 rows in 1 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 1 rows in 1 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 208 rows in 3 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 363 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 5 rows in 1 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 329 rows in 43 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 22 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 164 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 115 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 1 rows in 1 pages for object "sys.sysguidrefs".
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.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 101 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 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 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 8 rows in 1 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 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object "spt_fallback_db".
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object "spt_fallback_dev".
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object "spt_fallback_usg".
DBCC results for 'sys.queue_messages_1003150619'.
There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
DBCC results for 'sys.queue_messages_1035150733'.
There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
DBCC results for 'sys.queue_messages_1067150847'.
There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'spt_values'.
There are 2508 rows in 12 pages for object "spt_values".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC results for 'mssqlsystemresource'.
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 1124 rows in 11 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 240 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 252 rows in 3 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.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
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 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 20 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 3080 rows in 58 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 18058 rows in 336 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 2 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 5 rows in 1 pages for object "sys.syscerts".
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 540 rows in 10 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 808 rows in 5 pages for object "sys.sysiscols".
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 3751 rows in 1269 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 21 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 904 rows in 6 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 488 rows in 2 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 4 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 15829 rows in 119 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 487 rows in 4 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 739 rows in 4 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 322 rows in 2 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 929 rows in 7 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 852 rows in 26 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 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.syspalvalues'.
There are 599 rows in 5 pages for object "sys.syspalvalues".
DBCC results for 'sys.spt_server_info'.
There are 27 rows in 1 pages for object "sys.spt_server_info".
DBCC results for 'sys.spt_datatype_info'.
There are 76 rows in 1 pages for object "sys.spt_datatype_info".
DBCC results for 'sys.role_permissions'.
There are 165 rows in 2 pages for object "sys.role_permissions".
DBCC results for 'sys.sysbinpals'.
There are 45 rows in 1 pages for object "sys.sysbinpals".
DBCC results for 'sys.syscolrdb'.
There are 11969 rows in 180 pages for object "sys.syscolrdb".
DBCC results for 'sys.spt_provider_types'.
There are 32 rows in 1 pages for object "sys.spt_provider_types".
DBCC results for 'sys.syspalnames'.
There are 127 rows in 1 pages for object "sys.syspalnames".
DBCC results for 'sys.spt_permission_names'.
There are 5 rows in 1 pages for object "sys.spt_permission_names".
DBCC results for 'sys.sysobjrdb'.
There are 1935 rows in 26 pages for object "sys.sysobjrdb".
DBCC results for 'sys.spt_datatype_info_ext'.
There are 13 rows in 1 pages for object "sys.spt_datatype_info_ext".
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".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(затронута одна строка)

Время выполнения: 2021-06-18T16:41:16.2739694+03:00
`

Еще раз доброго дня @Anagramma

Суть проблемы я понял - побилась системная таблица и база не может отдать нужную инфу. Можно узнать в каком статусе эта база? Постараюсь на выходных что-то вменяемое выложить.

Еще раз доброго дня @Anagramma

Суть проблемы я понял - побилась системная таблица и база не может отдать нужную инфу. Можно узнать в каком статусе эта база? Постараюсь на выходных что-то вменяемое выложить.

База в обычном статусе (не Suspect (Подозрительный)) (если я правильно понимаю вопрос). Ожидаемое поведение программы - либо не выводить такую базу в списке баз, либо выводить, но с пометкой, что она с ошибкой(не подлежит обработке и т.п.), но это на ваше усмотрение.
Спасибо за этот софт и удачи в разработке!

Доброго дня @Anagramma

Увы не могу воспроизвести вашу проблему. Есть ли возможность скинуть вашу проблемную базу (бекап + исходные файлы) на почту sergiisyrovatchenko@gmail.com? Мне бы очень не хотелось ставить лишнюю заглушку в код и нормально вашу ситуацию исправить.

Заранее спасибо!

Доброго дня. Архив (ссылка) в выслан с anagramma gmail, не было отклика о получении.

Hi @Anagramma

Issue was fixed into master branch and will be available with v1.0.0.67 release.

Thanks!