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

Este artigo ajuda você a resolve 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 de KB original: 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: Restaurar o 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, 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 de spid61 do Datetime> : 9768, Severidade: 16, Estado: 1.
  <Datetime> spid61 Um usuário de banco de dados associado à conversa segura foi removido antes que as credenciais tivessem sido trocadas com o ponto de extremidade distante. Evite usar DROP USER enquanto as conversas estão sendo criadas.
  <Datetime> spid61 Falha ao marcar para notificações de consulta pendentes no banco de dados "5" devido ao seguinte erro ao abrir o banco de dados: "Um usuário de banco de dados associado à conversa segura foi removido antes que as credenciais tivessem sido trocadas com o ponto de extremidade distante. Evite usar DROP USER enquanto as conversas estão sendo criadas. Falha na operação de limpeza de assinaturas de notificação de consulta. Consulte erros anteriores para obter detalhes.'.
  <Erro de spid61 do Datetime> : 9001, Severidade: 16, Estado: 5.
  <Datatime> spid61 O log do banco de dados 'Test' não está disponível. Verifique o log de eventos em busca de mensagens de erro relacionadas. Resolva quaisquer erros e reinicie o banco de dados.
  <Erro de spid61 do Datetime> : 3314, Severidade: 21, Estado: 4.
  <Datetime> spid61 Durante a desfazer de uma operação registrada no banco de dados 'Test', ocorreu um erro na ID do registro de log (1835:7401:137). Normalmente, a falha específica é registrada anteriormente como um erro no serviço log de eventos do Windows. Restaure o banco de dados ou o arquivo de um backup ou repare o banco de dados.

  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: restaurar o banco de dados de seu backup pode levar muito tempo e mensagens semelhantes às seguintes são registradas no log de erros SQL Server:

  A entrega de notificação de consulta SPID da Hora da Data 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 agente de serviço: "O identificador de conversa "<Manipulador> de Conversas" 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.

Motivo

Causa do Sintoma 1: quando você especifica NEW_BROKER opção durante a operação de restauração, SQL Server tenta truncar todas as tabelas relacionadas ao Service Broker. A truncamento requer SCH_M bloqueio no objeto truncado. A transação main mantém um bloqueio SCH_M no sysdesend. Quando um banco de dados é recuperado ou restaurado, por padrão SQL Server tenta disparar todas as notificações de consulta pendentes, o que exige que linhas(mensagens) sejam inseridas na tabela sysdesend. Essa operação requer um bloqueio SCH_S na tabela. No entanto, essa operação acontece em uma transação diferente e a tentativa de adquirir SCH_S bloqueio é bloqueada pelo bloqueio SCH_M detido pela primeira transação. Como resultado, o thread que executa a restauração agora está bloqueado em um recurso que ele possui, situação conhecida como auto-impasse. O impasse é detectado pelo monitor de impasse 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 devido a problemas conhecidos documentados nos artigos de correção mencionados na seção Resolução abaixo.

Resolução

Solução alternativa para Sintoma 1: você pode contornar o problema habilitando o sinalizador de rastreamento no nível da sessão 9109 antes de tentar a operação de restauração. Um script de exemplo é 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ê marcar para garantir que as notificações de consulta estejam sendo disparadas. A maneira mais fácil de conseguir isso é alterar o status do banco de dados para Somente leitura e alterá-lo de volta para Leitura-gravação. Algumas outras maneiras que você pode marcar para isso incluem desanexar e recolocar o banco de dados, reiniciar SQL Server etc.

Você também pode evitar o problema completamente não especificando ao não especificar a opção NEW_BROKER na operação de restauração e, em vez disso, usar ALTER DATABASE com NEW_BROKER opção após a restauração do banco de dados.

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