none
estou com o meu banco em modo EMERGENCY RRS feed

Respostas

  • Bom Dia,

    Novamente eu proponho cautela, respirar fundo e pensar no que fazer e não sair fazendo. Se o seu banco conseguir se estabelecer no modo de emergência, seja cauteloso e antes de executar qualquer comando, faça um export dos dados para um banco vazio. Isso vai permitir que você consiga salvar o que for possível (e normalmente é possível salvar muita coisa).

    Reconstruir índices, rodar comandos DBCC (REPAIR_REBUILD e REPAIR_ALLOW_DATA_LOSS), etc são úteis, mas não devem ser sua primeira alternativa. Veja que reconstruir o índice pode inclusive piorar o problema e aí você não terá mais a opção de copiar os dados se as coisas saírem do controle. Não acho que o DBCC e o ALTER INDEX são ruins. Só acho que estão fora de ordem. Copie os dados antes, execute os comandos depois.

    A julgar pelos erros, muito provavelmente sua base não irá voltar a funcionar. O melhor mesmo é criar um base vazia e exportar tudo o que puder.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível



    quarta-feira, 25 de abril de 2012 13:20

Todas as Respostas

  • Não apareceu nenhuma mensagem de "xxxx found 0 errors xxx" na execução do DBCC CHECKDB na database?

    Já viu no arquivo de log do SQL Server?

    Se seu DBCC rodar sem problema e sem erros tenta deixá-la online.

    ALTER DATABASE arraialeletrica_sql SET ONLINE


    Fabrício França Lima | MCP, MCTS, MCITP | Visit my site: http://fabriciolima.net | Dicas de artigos SQL: follow me on Twitter - @fabriciodba.

    terça-feira, 24 de abril de 2012 21:08
  • DBCC CHECKDB(arraialeletricafs_sql) WITH ALL_ERRORMSGS, NO_INFOMSGS execultei pra filtrar as mensagens com erro o resultado foi esse Msg 8904, Level 16, State 1, Line 1 Extent (1:20112) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20112) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20232) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20232) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20240) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20240) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20248) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20248) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20256) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20256) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20272) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20272) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20280) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20280) is allocated to 'GAM' and at least one other object. Msg 8904, Level 16, State 1, Line 1 Extent (1:20288) in database ID 11 is allocated by more than one allocation object. Msg 8913, Level 16, State 1, Line 1 Extent (1:20288) is allocated to 'GAM' and at least one other object. CHECKDB found 16 allocation errors and 0 consistency errors not associated with any single object. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for page (1:2581) in object ID 534449128, index ID 0, partition ID 72057594150322176, alloc unit ID 72057594164740096 (type In-row data). Expected value 80_PCT_FULL, actual value 0_PCT_FULL. CHECKDB found 0 allocation errors and 1 consistency errors in table 'MOSTRAR_COLUNAS' (object ID 534449128). Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for page (1:17706) in object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049855488 (type In-row data). Expected value 100_PCT_FULL, actual value 95_PCT_FULL. Msg 8913, Level 16, State 3, Line 1 Extent (1:20272) is allocated to 'dbo.HISTORICO' and at least one other object. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for page (1:8932) in object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). Expected value 100_PCT_FULL, actual value 50_PCT_FULL. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 26, text ID 53281030144 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 28, text ID 53281095680 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 30, text ID 53281161216 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 32, text ID 53281226752 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 34, text ID 53281292288 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 36, text ID 53281357824 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 38, text ID 53281423360 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 40, text ID 53281488896 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 42, text ID 53281554432 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 658101385, index ID 0, partition ID 72057594045267968, alloc unit ID 72057594049921024 (type LOB data). The off-row data node at page (1:8932), slot 43, text ID 53281619968 is not referenced. CHECKDB found 1 allocation errors and 12 consistency errors in table 'HISTORICO' (object ID 658101385). Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 665925594, index ID 1, partition ID 72057594149208064, alloc unit ID 72057594162708480 (type In-row data). Page (1:17560) is missing a reference from previous page (1:20288). Possible chain linkage problem. Msg 8913, Level 16, State 3, Line 1 Extent (1:20288) is allocated to 'dbo.ITENS_NF_VENDA' and at least one other object. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 665925594, index ID 1, partition ID 72057594149208064, alloc unit ID 72057594162708480 (type In-row data). Page (1:20288) was not seen in the scan although its parent (1:18496) and previous (1:17503) refer to it. Check any previous errors. CHECKDB found 1 allocation errors and 2 consistency errors in table 'ITENS_NF_VENDA' (object ID 665925594). Msg 8981, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 1, partition ID 72057594144096256, alloc unit ID 72057594156875776 (type In-row data). The next pointer of (1:18484) refers to page (1:20056). Neither (1:20056) nor its parent were encountered. Possible bad chain linkage. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 4, partition ID 72057594144292864, alloc unit ID 72057594157072384 (type In-row data). Page (1:11088) is missing a reference from previous page (1:20256). Possible chain linkage problem. Msg 8913, Level 16, State 3, Line 1 Extent (1:20256) is allocated to 'dbo.ITENS_NF_COMPRA, IDX_ITNFCOMPRA_03' and at least one other object. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 4, partition ID 72057594144292864, alloc unit ID 72057594157072384 (type In-row data). Page (1:20256) was not seen in the scan although its parent (1:3378) and previous (1:19920) refer to it. Check any previous errors. Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 8, partition ID 72057594144555008, alloc unit ID 72057594157334528 (type In-row data). The previous link (1:20248) on page (1:2113) does not match the previous page (1:10881) that the parent (1:19930), slot 10 expects for this page. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 8, partition ID 72057594144555008, alloc unit ID 72057594157334528 (type In-row data). Page (1:2113) is missing a reference from previous page (1:20248). Possible chain linkage problem. Msg 8913, Level 16, State 3, Line 1 Extent (1:20248) is allocated to 'dbo.ITENS_NF_COMPRA, IDX_ITNFCOMPRA_07' and at least one other object. Msg 8981, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 8, partition ID 72057594144555008, alloc unit ID 72057594157334528 (type In-row data). The next pointer of (1:10881) refers to page (1:20248). Neither (1:20248) nor its parent were encountered. Possible bad chain linkage. Msg 8981, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 9, partition ID 72057594144620544, alloc unit ID 72057594157400064 (type In-row data). The next pointer of (1:12657) refers to page (1:5682). Neither (1:5682) nor its parent were encountered. Possible bad chain linkage. Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 9, partition ID 72057594144620544, alloc unit ID 72057594157400064 (type In-row data). The previous link (1:5682) on page (1:7624) does not match the previous page (1:12657) that the parent (1:4808), slot 30 expects for this page. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 9, partition ID 72057594144620544, alloc unit ID 72057594157400064 (type In-row data). Page (1:7624) is missing a reference from previous page (1:5682). Possible chain linkage problem. Msg 8936, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 12, partition ID 72057594144817152, alloc unit ID 72057594157596672 (type In-row data). B-tree chain linkage mismatch. (1:5661)->next = (1:19032), but (1:19032)->Prev = (1:20112). Msg 8913, Level 16, State 3, Line 1 Extent (1:20112) is allocated to 'dbo.ITENS_NF_COMPRA, _dta_index_ITENS_NF_COMPRA_5_1125579048__K7_K39_K1_K62_K3_K27_12_13_14_20_26_37_66_67_69_70_72_74' and at least one other object. Msg 8980, Level 16, State 1, Line 1 Table error: Object ID 1120879210, index ID 12, partition ID 72057594144817152, alloc unit ID 72057594157596672 (type In-row data). Index node page (1:11839), slot 138 refers to child page (1:20112) and previous child (1:5661), but they were not encountered. CHECKDB found 3 allocation errors and 11 consistency errors in table 'ITENS_NF_COMPRA' (object ID 1120879210). Msg 8979, Level 16, State 1, Line 1 Table error: Object ID 1765073524, index ID 1, partition ID 72057594159300608, alloc unit ID 72057594174439424 (type In-row data). Page (1:473) is missing references from parent (unknown) and previous (page (0:0)) nodes. Possible bad root entry in system catalog. Msg 8948, Level 16, State 1, Line 1 Database error: Page (1:7824) is marked with the wrong type in PFS page (1:1). PFS status 0x0 expected 0x60. Msg 8979, Level 16, State 1, Line 1 Table error: Object ID 1765073524, index ID 1, partition ID 72057594159300608, alloc unit ID 72057594174439424 (type In-row data). Page (1:7824) is missing references from parent (unknown) and previous (page (1:473)) nodes. Possible bad root entry in system catalog. Msg 8906, Level 16, State 1, Line 1 Page (1:19358) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:16176), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'. Msg 8980, Level 16, State 1, Line 1 Table error: Object ID 1765073524, index ID 1, partition ID 72057594159300608, alloc unit ID 72057594174439424 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:19358) and previous child (0:0), but they were not encountered. CHECKDB found 2 allocation errors and 3 consistency errors in table 'CHANGES' (object ID 1765073524). Msg 8913, Level 16, State 3, Line 1 Extent (1:20232) is allocated to 'dbo.LANCAMENTOS_DE_ESTOQUE' and at least one other object. Msg 8981, Level 16, State 1, Line 1 Table error: Object ID 1856881832, index ID 1, partition ID 72057594145013760, alloc unit ID 72057594157793280 (type In-row data). The next pointer of (1:20147) refers to page (1:20232). Neither (1:20232) nor its parent were encountered. Possible bad chain linkage. Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 1856881832, index ID 2, partition ID 72057594145079296, alloc unit ID 72057594157858816 (type In-row data). The previous link (1:10656) on page (1:787) does not match the previous page (1:20240) that the parent (1:2322), slot 141 expects for this page. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 1856881832, index ID 2, partition ID 72057594145079296, alloc unit ID 72057594157858816 (type In-row data). Page (1:787) is missing a reference from previous page (1:10656). Possible chain linkage problem. Msg 8913, Level 16, State 3, Line 1 Extent (1:20240) is allocated to 'dbo.LANCAMENTOS_DE_ESTOQUE, _dta_index_LANCAMENTOS_DE_ESTOQUE_10_674101442__K11_K4_18' and at least one other object. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 1856881832, index ID 2, partition ID 72057594145079296, alloc unit ID 72057594157858816 (type In-row data). Page (1:20240) was not seen in the scan although its parent (1:2322) and previous (1:10656) refer to it. Check any previous errors. Msg 8913, Level 16, State 3, Line 1 Extent (1:20280) is allocated to 'dbo.LANCAMENTOS_DE_ESTOQUE, idx_data_usuario' and at least one other object. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 1856881832, index ID 6, partition ID 72057594145341440, alloc unit ID 72057594158120960 (type In-row data). Page (1:20280) was not seen in the scan although its parent (1:2260) and previous (1:20040) refer to it. Check any previous errors. CHECKDB found 3 allocation errors and 5 consistency errors in table 'LANCAMENTOS_DE_ESTOQUE' (object ID 1856881832). Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 1956358184, index ID 1, partition ID 72057594141343744, alloc unit ID 72057594153205760 (type In-row data). Page (1:4765) was not seen in the scan although its parent (1:14541) and previous (1:19936) refer to it. Check any previous errors. CHECKDB found 0 allocation errors and 1 consistency errors in table 'ESTOQUE' (object ID 1956358184). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20200:5) identified by (CONTROLE_ID = 2846494) with index values (ESTOQUE_ID = 3932 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-03-19 17:22:00.000' and CONTROLE_ID = 2846494). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20205:20) identified by (CONTROLE_ID = 2846679) with index values (ESTOQUE_ID = 867 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-04-14 12:20:00.000' and CONTROLE_ID = 2846679). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20205:23) identified by (CONTROLE_ID = 2846682) with index values (ESTOQUE_ID = 866 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-04-14 12:20:00.000' and CONTROLE_ID = 2846682). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20205:24) identified by (CONTROLE_ID = 2846683) with index values (ESTOQUE_ID = 867 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-04-14 12:20:00.000' and CONTROLE_ID = 2846683). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20205:25) identified by (CONTROLE_ID = 2846684) with index values (ESTOQUE_ID = 869 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-04-14 12:21:00.000' and CONTROLE_ID = 2846684). Msg 8951, Level 16, State 1, Line 1 Table error: Table 'CONTROLE_FISCAL' (ID 2095450639). Data row does not have a matching index row in index '_dta_index_CONTROLE_FISCAL_8_1317579732__K2_K6_K16_K4_3_9' (ID 12). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:20205:30) identified by (CONTROLE_ID = 2846689) with index values (ESTOQUE_ID = 868 and CANCELADO = 0 and FILIAL = 1 and DATA = '2012-04-14 12:22:00.000' and CONTROLE_ID = 2846689). CHECKDB found 0 allocation errors and 6 consistency errors in table 'CONTROLE_FISCAL' (object ID 2095450639). CHECKDB found 26 allocation errors and 41 consistency errors in database 'arraialeletricafs_sql'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (arraialeletricafs_sql).


    terça-feira, 24 de abril de 2012 21:19
  • Amigo,

    Recontruye os indices do banco do dados y depois corre un dbcc checkdb e envia para nos os resultados


    Carlos Ignacio Aguero. DBA SQL Server. Toda mi respeto al pueblo Peruano por la ayuda prestada en la guerra de Malvinas.

    terça-feira, 24 de abril de 2012 22:06
  • tentei atraves de:
    sp_dboption
    'arraialeletrica_sql','single_user', true
    go
    DBCC CHECKALLOC ('arraialeletrica_sql',REPAIR_REBUILD)
    e me reportou 
    Msg 7901, Level 16, State 1, Line 3
    The repair statement was not processed. This level of repair is not supported when the database is in emergency mode.
    tem algum outro procedimento que eu possa fazer?

    quarta-feira, 25 de abril de 2012 11:19
  • Vai ter que usar o commando

    ALTER INDEX ALL ON NomeTabela REBUILD

    Para cada tabela que fica no dbcc checkdb, o banco do dado em single user


    Carlos Ignacio Aguero. DBA SQL Server. Toda mi respeto al pueblo Peruano por la ayuda prestada en la guerra de Malvinas.

    quarta-feira, 25 de abril de 2012 11:30
  • Fui tentar na primeira tabela 

    ALTER INDEX ALL ON GAM REBUILD

    e apresentou o seguinte erro

    Msg 1088, Level 16, State 9, Line 1

    Cannot find the object "GAM" because it does not exist or you do not have permissions.

    quarta-feira, 25 de abril de 2012 12:24
  • Bom Dia,

    Novamente eu proponho cautela, respirar fundo e pensar no que fazer e não sair fazendo. Se o seu banco conseguir se estabelecer no modo de emergência, seja cauteloso e antes de executar qualquer comando, faça um export dos dados para um banco vazio. Isso vai permitir que você consiga salvar o que for possível (e normalmente é possível salvar muita coisa).

    Reconstruir índices, rodar comandos DBCC (REPAIR_REBUILD e REPAIR_ALLOW_DATA_LOSS), etc são úteis, mas não devem ser sua primeira alternativa. Veja que reconstruir o índice pode inclusive piorar o problema e aí você não terá mais a opção de copiar os dados se as coisas saírem do controle. Não acho que o DBCC e o ALTER INDEX são ruins. Só acho que estão fora de ordem. Copie os dados antes, execute os comandos depois.

    A julgar pelos erros, muito provavelmente sua base não irá voltar a funcionar. O melhor mesmo é criar um base vazia e exportar tudo o que puder.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível



    quarta-feira, 25 de abril de 2012 13:20
  • realmente a base so salvou dados ate o dia 13 de abril, vou recuperar copiando os dados das tabelas para um novo banco e qualquer duvida volto a postar

    quarta-feira, 25 de abril de 2012 13:46