Partilhar via

A restauração ou recuperação pode falhar ou levar muito tempo se a notificação de consulta for usada em um banco de dados

  • Artigo

Este artigo ajuda a resolver o problema em que a restauração ou recuperação pode falhar ou levar muito tempo se a notificação de consulta for usada em um banco de dados.

Versão original do produto: SQL Server
Número original do KB: 2483090

Sintomas

Você pode observar um ou mais dos seguintes sintomas com um banco de dados configurado para assinaturas de notificação de consulta:

  • Sintoma 1: A restauração do banco de dados de seu backup pode falhar com a mensagem de erro 1205 se NEW_BROKER opção for especificada durante a operação de restauração. Além disso, os arquivos de despejo serão gerados na pasta Errorlog do SQL Server.

  • Sintoma 2: A restauração do banco de dados de seu backup falha e o banco de dados fica offline. Além disso, as seguintes mensagens são registradas no log de erros do SQL Server:

    <Erro spid61 de data e hora> : 9768, Gravidade: 16, Estado: 1.
    <Datetime> spid61 Um usuário de banco de dados associado à conversa segura foi descartado antes que as credenciais fossem trocadas com o ponto de extremidade distante. Evite usar DROP USER enquanto as conversas são criadas.
    <Datetime> spid61 Falha ao verificar se há notificações de consulta pendentes no banco de dados "5" devido ao seguinte erro ao abrir o banco de dados: 'Um usuário do banco de dados associado à conversa segura foi descartado antes que as credenciais fossem trocadas com o ponto de extremidade distante. Evite usar DROP USER enquanto as conversas são criadas. Falha na operação de limpeza das assinaturas de notificação de consulta. Consulte os erros anteriores para obter detalhes.'.
    <Erro spid61 de data e hora> : 9001, Gravidade: 16, Estado: 5.
    <Datetime> spid61 O log do banco de dados 'Test' não está disponível. Verifique o log de eventos para obter as mensagens de erro relacionadas. Resolva todos os erros e reinicie o banco de dados.
    <Erro spid61 de data e hora> : 3314, Gravidade: 21, Estado: 4.
    <Datetime> spid61 Durante a desexecução de uma operação registrada no banco de dados 'Test', ocorreu um erro na ID do registro de log (1835:7401:137). Em geral, a falha específica é registrada anteriormente como um erro no serviço Log de Eventos do Windows. Repare ou restaure o banco de dados usando um backup.

    Observação

    Você pode encontrar o problema durante a fase de recuperação do banco de dados. A recuperação também é executada em um banco de dados quando o banco de dados é colocado online, o servidor é reiniciado, etc.

  • Sintoma 3: A restauração do banco de dados de seu backup pode levar muito tempo e mensagens semelhantes às seguintes são registradas no log de erros do SQL Server:

    Data Hora A entrega de notificação de consulta SPID não pôde enviar mensagem na caixa de diálogo '{ ID da caixa de diálogo }.'. Falha na entrega para notificação '?<qn:QueryNotification xmlns:qn="https://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="2881" type="change" source="database" info="restart" database_id="7" sid="0x010500000000000515000000FA48F22A6990BA52422C73DFF9030000"><qn:Message>4a4c696b-645c-40fd-bfef-4f2bc7c599b4; eb99973e-3cc9-4c7e-b4b9-47d8cf590c43</qn:Message></qn:QueryNotification>' devido ao seguinte erro no Service Broker: 'O identificador de conversação "<Manipulador de conversação>" não foi encontrado.'.

    Observação

    Você pode encontrar o problema durante a fase de recuperação do banco de dados. A recuperação também é executada em um banco de dados quando o banco de dados é colocado online, o servidor é reiniciado, etc.

Causa

Causa do sintoma 1: quando você especifica NEW_BROKER opção durante a operação de restauração, o SQL Server tenta truncar todas as tabelas relacionadas ao Service Broker. O truncamento requer SCH_M bloqueio no objeto truncado. A transação principal, portanto, mantém um bloqueio de SCH_M no sysdesend. Quando um banco de dados é recuperado ou restaurado, por padrão, o SQL Server tenta disparar todas as notificações de consulta pendentes, o que requer que rows(messages) sejam inseridos na tabela sysdesend. Esta operação requer um bloqueio SCH_S na mesa. No entanto, essa operação ocorre em uma transação diferente e a tentativa de adquirir SCH_S bloqueio é bloqueada pelo bloqueio SCH_M mantido pela primeira transação. Como resultado, o thread que executa a restauração agora está bloqueado em um recurso que possui, situação conhecida como auto-deadlock. O deadlock é detectado pelo monitor de deadlock e o thread é encerrado, encerrando assim a operação de restauração.

Para obter mais informações sobre bloqueios, consulte Modos de bloqueio. Os outros sintomas discutidos na seção Sintomas são causados por problemas conhecidos documentados nos artigos de correção mencionados na seção Resolução abaixo.

Solução

Solução alternativa para o Sintoma 1: Você pode contornar o problema ativando o sinalizador de rastreamento no nível da sessão 9109 antes de tentar a operação de restauração. Um exemplo de script é mostrado abaixo:

dbcc traceon (9109)
go
RESTORE DATABASE [Test] 
FROM DISK = N'C:\TestBackup.bak' WITH FILE = 1, 
MOVE N'test_Data' TO N'C:\test.mdf', 
MOVE N'test_Log' TO N'C:\test_1.ldf', 
NOUNLOAD, 
STATS = 1, 
NEW_BROKER
go
dbcc traceoff (9109)
go

Observação

Depois que o banco de dados for completamente restaurado ou recuperado, é altamente recomendável que você verifique se as notificações de consulta estão sendo disparadas. A maneira mais fácil de conseguir isso é alterar o status do banco de dados para Somente leitura e alterá-lo novamente para Leitura/gravação. Algumas outras maneiras de verificar isso incluem desanexar e reanexar o banco de dados, reiniciar o SQL Server etc.

Você também pode evitar o problema completamente não especificando a opção NEW_BROKER na operação de restauração e, em vez disso, usar ALTER DATABASE com NEW_BROKER opção depois que o banco de dados for restaurado.

Para obter mais informações, consulte DBCC TRACEON – Sinalizadores de rastreamento (Transact-SQL).