locked
Distribution clean up error RRS feed

  • Pergunta

  • Pessoal, Utilizo Replicação de dados com sql server 2000.  Nos ultimos 5 dias o job clean up que faz a limpeza da tabela msrepl_commands esta dando o erro abaixo:

    Message
    Executed as user: domain\user. The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. [SQLSTATE HY000] (Error 1204).  The step failed.

    A mensagem de erro ate que é clara, porem se é um job do proprio SQL Server não deveria dar este tipo de problema. Antes de começar a acontecer este problema a tabela tinha 20 milhoes de registros, ai fui fazendo enxuga manual na msrepl_commands e agora ela tem cerca de 3 milhoes (para nosso ambiente é um numero pequeno). Ao executar o job acontece varios bloqueios no ambiente e os jobs logreader e distribution todos falham.

    Alguem tem alguma dica de como proceder com este caso.

    Obrigado,

    quarta-feira, 28 de julho de 2010 11:37

Respostas

  • Boa Tarde,

    Entendo que restrições de memória e processos de limpeza possam exercer certa pressão sobre o conjunto, mas não creio que processos como o Lazy Writer estejam provocando bloqueios. A limpeza de páginas sujas através de sua gravação no disco se dá através de Latches e não de bloqueios (Locks). Embora Latches e bloqueios sejam analogamente semelhantes eles não são a mesma coisa e nem provocam os mesmos efeitos.

    A julgar pela mensagem de erro, me parece haver um problema relacionado a bloqueios, ou seja, por alguma razão o SQL Server não consegue obter todos os bloqueios necessários para conseguir expurgar os registros desejados. Isso pode acontecer por ausência de memória para poder "montar" os bloqueios, mas pela consideração de adição de memória não parece ser o caso.

    Apenas a título de curiosidade é possível colocar a rentenção mínima de 1 dia ao invés de ser zero ? Isso talvez pressionasse os comandos a ficarem pelo menos um dia no distributor e o cleanup não iria concorrer com a leitura de transações para replicá-las. Não tenho certeza dessa abordagem, mas é uma tentativa.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Truncate versus Delete - Uma explicação mais detalhada
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1112.entry 


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 28 de julho de 2010 21:01
  • Olá Dobereiner,

    Sem querer desviar o assunto principal da thread, gostaria de dizer que acho muito pertinente as análises feitas até aqui, mas fiquei um pouco em dúvida sobre suas colocações.

    "O lock trava uma página suja para não ser alterada durante seu despejo e o latch (não realiza a gravação em disco, para melhor detalhe pesquisar sobre a API: WriteFileGather). Os latches apenas garantem a integridade transacional dos dados, os locks garantem a integridade física."

    Se observarmos as anotações no SQL Server 2008 Internals (página 349) há uma contradição com sua afirmação:

    "A latch is acquired while a page is being read from or written to disk and protects the physical integrity of the contents of the page."

    O livro SQL Server 2008 Internals & Troubleshooting também defende que o lath está ligado mais a questões físicas enquanto os locks estão ligados mais a controle transacional

    "While similar, locks and latches are trying to achieve very different goals. A lock is present to uphold a transaction’s integrity; the latch’s role is to protect the database’s physical integrity and to co-ordinate threading in the engine."

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Truncate versus Delete - Uma explicação mais detalhada
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1112.entry 


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 29 de julho de 2010 03:45

Todas as Respostas

  • Olá Eudes,

     

    Que tipo de replicação voce tem?

    O intervalo de execução desse job mudou? Qual é o intervalo de execução desse job?

     

    Talvez como solução alternativa: Como ele causa lock nos outros agents, é possivel dar uma parada nos outros agents, executar o job e depois de concluido, voce poderia diminuir o tempo entres os intervalos de execução desse job, e dar um start nos outros agents, assim o numero de linhas nessa tabela não subiria tanto o que causa demora no delete e consequente lock nos outros jobs.

     

    []´s


    quarta-feira, 28 de julho de 2010 12:28
  • Obrigado pela resposta Leandro.

    Eu utilizo neste caso replicação transacional, o job não teve o agendamento que é de 15 minutos alterado. O mais estranho é que não alteramos nada no ambiente e começou a dar estes erros e, mesmo reduzindo o tamanho da tabela não resolveu.

    Acontece que tenho cerca de 50 publicações e 5 logreader nesta instancia e não poderia parar todos estes agentes para rodar o clean up. Estou fazendo uma limpeza manual na tabela, mas preciso que o clean up volte a fazer esta tarefa pra não comprometer o ambiente.

    Obrigado,

    Eudes.

    quarta-feira, 28 de julho de 2010 13:07
  • Bom dia Eudes, tudo bem?

    Eudes a primeira coisa que devemos fazer é identificar o bloqueio que está acontencendo no seu ambiente.

    O que consegui entender é que o processo de limpeza manual está funcionando, e o processo automático não. A versão do sql é mais antiga com a que eu trabalho e vou ter que procurar em documentações sobre alterações na estrutura da replicação 2000 vs 2k5 ou 2k8.

    Existem dois comandos que conversando com alguns amigos, eles existiam no sql server 200 também. Segue:

    DBCC MEMORYSTATUS --> Este comando ele tira um snapshot do que o serviço do sql server está usando na memória. Últil para identificarmos problemas com recursos.

    DBCC OPENTRAN --> Vai listar transações antigas em seu ambiente. O segredo deste comando está na capacidade de olhar direto para o LOG do sql server e procurar por transações ativas. OU seja, se nenhum resultado aparecer, devemos observar para melhorias na engine que evitam a gravação de log (ganho em velocidade por evitar io);

    Você precisará de direitos sysadmin para executá-los.

    Poste os resultados que podemos lhe ajudar.


    Atenciosamente,

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com

     

     


    Aquilo que sou é aquilo que me foi outorgado
    quarta-feira, 28 de julho de 2010 14:55
  • Olá Dobereiner,

    Consigo fazer uma limpeza manual sim, pois faço aos poucos evitando os tais locks, mas o processo automático "Distribution Clean Up" sempre me retorna os mesmos erros.   Problemas com a memoria do servidor acredito que não seja, pois aumentamos a memoria fisica do equipamento depois que começamos a ter estes erros.

    Executando o DBCC OPENTRAN, recebo a informação de uma sessão que acredito ser do clean up, como segue abaixo:

    Transaction

    information for database 'distribution'.

    Oldest active

    transaction:

    SPID

    (server process ID) : 127

    UID

    (user ID) : -1

    Name

    : DML

    LSN

    : (796218:7022:1)

    Start time

    : Jul 28 2010 2:34:01:900PM

     

     

    execution completed. If DBCC printed error messages, contact your system administrator.

    Obrigado,

    quarta-feira, 28 de julho de 2010 17:43
  • Boa tarde,

    Ok, tem como você obter o comando que este spid executou?

    Eu peço desculpas pelo fato de esquecer de comentar 1 coisa:

    1 - Executar o comando dbcc opentran ao executar o processo automático (se possível);

    O comando dbcc memorystatus sofreu uma evolução nas versões recentes do sql 2k5/2k8. Com ele é possível identificarmos páginas sujas, quantidade de dados para planos de execuçoes dentre outros.

    Ele é apenas uma espécie de dump sobre o serviço. Eu apenas gostaria de ver o resultado sobre a memória, porque resumindo, os bloqueios são gerenciados internamente no sql server através de bloqueios de páginas sujas, pois o sql server deve bloquear estas páginas, verificar se precisam ser escritas e proceder com uma função de IO para escrevê-las, caso necessite. Quem verifica este processo é o Lazy Write, uma das 3 grandes funções de gerenciamento de bloqueios no sql.

    E tendo idéia da quantidade de páginas sujas em relação a outros valores de sua base, facilita identificar possíveis problemas.

    Atenciosamente,

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


    Aquilo que sou é aquilo que me foi outorgado
    quarta-feira, 28 de julho de 2010 18:23
  • Ok,

    O comando executado é uma procedure que faz a limpeza da tabela repl_commands
    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

    Eu executei o comando dbcc opentran justamente enquanto rodava o job automatico, o retorno foi postado anteriormente.

    segue abaixo o resultado do dbcc memorystatus se puder ajudar a analisar eu agradeço.

    Buffer Distribution            Buffers
    ------------------------------ -----------
    Stolen                         8922
    Free                           2620
    Procedures                     24422
    Inram                          0
    Dirty                          99526
    Kept                           0
    I/O                            0
    Latched                        246
    Other                          2485320

    (9 row(s) affected)

    Buffer Counts                  Buffers
    ------------------------------ -----------
    Commited                       2621056
    Target                         2621056
    Hashed                         2585092
    InternalReservation            756
    ExternalReservation            3639
    Min Free                       968
    Visible                        181544

    (7 row(s) affected)

    Procedure Cache                Value
    ------------------------------ -----------
    TotalProcs                     5237
    TotalPages                     24422
    InUsePages                     11987

    (3 row(s) affected)

    Dynamic Memory Manager         Buffers
    ------------------------------ -----------
    Stolen                         32504
    OS Reserved                    1096
    OS Committed                   1074
    OS In Use                      1055
    General                        2517
    QueryPlan                      24329
    Optimizer                      0
    Utilities                      140
    Connection                     876

    (9 row(s) affected)


    Global Memory Objects          Buffers
    ------------------------------ -----------
    Resource                       1690
    Locks                          4865
    XDES                           51
    SQLCache                       196
    Replication                    74
    LockBytes                      2
    ServerGlobal                   32

    (7 row(s) affected)


    Query Memory Objects           Value
    ------------------------------ -----------
    Grants                         3
    Waiting                        0
    Available (Buffers)            116679
    Maximum (Buffers)              121158

    (4 row(s) affected)

    Optimization Queue             Value
    ------------------------------ -----------
    Optimizing                     0
    Waiting                        0
    Available                      32
    Maximum                        32

    (4 row(s) affected)

    ected)

    quarta-feira, 28 de julho de 2010 20:26
  • Eudes,

     

    Acredito que pode estar ocorrendo um table lock por que ha um grande numero de linhas a serem deletadas nessa tabela, tente ver quantos comandos são gerados pelas transações nestes 15 min, voce pode fazer um join com a tabela MSrepl_transactions nela tem a coluna "entry_time" que é a hora que a transação entrou no distribution database.

    Talvez 15 min sejam muito altos e ai cada delete executado neste intervalo pega um grande num de linhas e consequentemente gera o lock nos outros agents.

    Quando voce executa os deletes manuais voce pega que quantidade de linhas? Qto tempo demora seus deletes? Qual o intervalo?

     

    []´s

    quarta-feira, 28 de julho de 2010 20:52
  • Boa Tarde,

    Entendo que restrições de memória e processos de limpeza possam exercer certa pressão sobre o conjunto, mas não creio que processos como o Lazy Writer estejam provocando bloqueios. A limpeza de páginas sujas através de sua gravação no disco se dá através de Latches e não de bloqueios (Locks). Embora Latches e bloqueios sejam analogamente semelhantes eles não são a mesma coisa e nem provocam os mesmos efeitos.

    A julgar pela mensagem de erro, me parece haver um problema relacionado a bloqueios, ou seja, por alguma razão o SQL Server não consegue obter todos os bloqueios necessários para conseguir expurgar os registros desejados. Isso pode acontecer por ausência de memória para poder "montar" os bloqueios, mas pela consideração de adição de memória não parece ser o caso.

    Apenas a título de curiosidade é possível colocar a rentenção mínima de 1 dia ao invés de ser zero ? Isso talvez pressionasse os comandos a ficarem pelo menos um dia no distributor e o cleanup não iria concorrer com a leitura de transações para replicá-las. Não tenho certeza dessa abordagem, mas é uma tentativa.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Truncate versus Delete - Uma explicação mais detalhada
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1112.entry 


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 28 de julho de 2010 21:01
  • Boa noite,

    relamente o lazy write não provocam o bloqueio.. é fato..

    Como dito ele é uma das 3 grandes funções do sql server que transferem páginas para o disco (automaticamente gerenciamento de locks). Sendo dividios em lazy, check e eager, embora os 3 executem a mesma rotina interna para no buffer para completarem a transferência.

    Locks e latches realmente são questões diferentes. O lock trava uma página suja para não ser alterada durante seu despejo e o latch (não realiza a gravação em disco, para melhor detalhe pesquisar sobre a API: WriteFileGather). Os latches apenas garantem a integridade transacional dos dados, os locks garantem a integridade física .

    E após o resultado do comando dbcc memorystatus, realmente vejo que o problema não está na memória, pois temos um total de buffer: 2621056

    Enquanto para dirty e stolen pages temos respectivamente: 99526 e 8922. Muito pouco em relação ao buffer.

    Quanto ao restante concordo com o testes.

    Atenciosamente,

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


     


    Aquilo que sou é aquilo que me foi outorgado
    • Editado Dobereiner Miller quarta-feira, 28 de julho de 2010 23:58 texto selecionado e pressionada a tecla "." que substituiu o texto selecionado.
    quarta-feira, 28 de julho de 2010 23:49
  • Olá Dobereiner,

    Sem querer desviar o assunto principal da thread, gostaria de dizer que acho muito pertinente as análises feitas até aqui, mas fiquei um pouco em dúvida sobre suas colocações.

    "O lock trava uma página suja para não ser alterada durante seu despejo e o latch (não realiza a gravação em disco, para melhor detalhe pesquisar sobre a API: WriteFileGather). Os latches apenas garantem a integridade transacional dos dados, os locks garantem a integridade física."

    Se observarmos as anotações no SQL Server 2008 Internals (página 349) há uma contradição com sua afirmação:

    "A latch is acquired while a page is being read from or written to disk and protects the physical integrity of the contents of the page."

    O livro SQL Server 2008 Internals & Troubleshooting também defende que o lath está ligado mais a questões físicas enquanto os locks estão ligados mais a controle transacional

    "While similar, locks and latches are trying to achieve very different goals. A lock is present to uphold a transaction’s integrity; the latch’s role is to protect the database’s physical integrity and to co-ordinate threading in the engine."

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Truncate versus Delete - Uma explicação mais detalhada
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1112.entry 


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 29 de julho de 2010 03:45
  • Boa noite Gustavo.. exatamente isso... ou seja, o contrário do que eu disse.. inverti a informação entre lock e latch, o latch faz o que escrevi sobre o lock e lock o que faz o latch.

    ;)

    Mas vale todo tipo de troca de informação.

    Ia passar despercebido.

    Atenciosamente,

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


    Aquilo que sou é aquilo que me foi outorgado
    quinta-feira, 29 de julho de 2010 04:16
  • Pessoal, voltando a minha thread.

    Leandro,  Realmente o volume de transações é alto, porem um fato estranho é que reduzimos este volume quando começamos a enfrentar este problema e, mesmo assim continua acontecendo estas mensagens de erro.

    Reduzindo o intervalo do job, as vezes o clean up até roda, porem ele causa lock na msrepl_commands e dá o mesmo erro na execução do Log reader e dos Jobs de distribution. (Fica muito intermitente executa uma vez com sucesso e umas 5 com erro.)

    Quando eu executo os deletes manuais eu pego 1000 transações da tabela MSrepl_transactions, mais estas transações geralmente vão dar 10.000 ou 15.000 comandos na msrepl_commands. Estes deletes manuais são rapidos cerca de 40 ou 50 segundos e com intervalos de uns 20 segundos entre um e outro.

    Gustavo, A retenção minina é de 11 ou 12 horas eu vou alterando este valor para gerenciar a quantidade de comandos na tabela msrepl_commands, antes este valor era de 72 horas.

    Dobereiner,  Também creio que o problema não é memoria, mas também não sei o que acontece.

    A unica solução que encontrei ate o momento é fazer os deletes limitados na msrepl_transaction da maneira que eu faço manual. Vou fazendo desta forma até conseguir resolver este caso ...

    Obrigado a todos pelas respostas.

    Eudes.

    sexta-feira, 30 de julho de 2010 21:16
  • Olá pessoal,blz?

    Faz o maior tempão que não atuo com replicação, mas, lembro que isso pode estar relacionado a problema de performance nas tabelas de transações da replicação.

    As tabelas de transações das replicações são como qualquer outra, se vc tiver problema de contenção devido ao volume muito alto de transações em milhões de registros com várias threads executando transações em paralelo no mesmo lugar(no gargalo)...muita espera....então, vc provavelmente se depararia com problemas de bloqueios mesmo, agora o que agrava é ter problema de indices ou estatísticas faltando ou desatualizadas nas principais tabelas de controle da replicação.

    Dá uma verificada nos índices, estatísticas...

    Grande abraço a todos,

     

     

     


    Regards...Emanuel Peixoto. Press Yes if the post is useful.
    quarta-feira, 11 de agosto de 2010 19:20