La restauración o recuperación pueden producir un error o tardar mucho tiempo si se usa la notificación de consulta en una base de datos

Este artículo le ayuda a resolver el problema por el que la restauración o la recuperación pueden producir errores o tardar mucho tiempo si se usa la notificación de consulta en una base de datos.

Versión del producto original: SQL Server
Número de KB original: 2483090

Síntomas

Puede observar uno o varios de los síntomas siguientes con una base de datos configurada para suscripciones de notificación de consulta:

• Síntoma 1: La restauración de la base de datos desde su copia de seguridad puede producir un error con el mensaje de error 1205 si se especifica NEW_BROKER opción durante la operación de restauración. Además, los archivos de volcado se generarán en la carpeta Errorlog del SQL Server.

• Síntoma 2: Se produce un error al restaurar la base de datos desde su copia de seguridad y la base de datos se desconecta. Además, los siguientes mensajes se registran en el registro de errores de SQL Server:

  <Datetime> spid61 Error: 9768, Gravedad: 16, Estado: 1.
  <Datetime> spid61 Se quitó un usuario de base de datos asociado a la conversación segura antes de que se intercambiara las credenciales con el extremo lejano. Evite usar DROP USER mientras se crean conversaciones.
  <Datetime> spid61 No se pudo comprobar si hay notificaciones de consulta pendientes en la base de datos "5" debido al siguiente error al abrir la base de datos: "Se quitó un usuario de base de datos asociado a la conversación segura antes de que se intercambiara las credenciales con el extremo lejano. Evite usar DROP USER mientras se crean conversaciones. Error en la operación de limpieza de suscripciones de notificación de consulta. Consulte los errores anteriores para obtener más información.'.
  <Datetime> spid61 Error: 9001, Gravedad: 16, Estado: 5.
  <Datetime> spid61 El registro de la base de datos "Test" no está disponible. Compruebe el registro de eventos para ver si hay mensajes de error relacionados. Resuelva los errores y reinicie la base de datos.
  <Datetime> spid61 Error: 3314, Gravedad: 21, Estado: 4.
  <Datetime> spid61 Durante la deshacer una operación registrada en la base de datos "Test", se produjo un error en el identificador de registro (1835:7401:137). Normalmente, el error específico se registra previamente como un error en el servicio de registro de eventos de Windows. Restaure la base de datos o el archivo desde una copia de seguridad o repare la base de datos.

  Nota:

  Puede encontrar el problema durante la fase de recuperación de la base de datos. La recuperación también se ejecuta en una base de datos cuando la base de datos se conecta, se reinicia el servidor, etc.

• Síntoma 3: Restaurar la base de datos desde su copia de seguridad puede tardar mucho tiempo y los mensajes similares a los siguientes se registran en SQL Server registro de errores:

  La entrega de notificaciones de consulta SPID de fecha y hora no pudo enviar el mensaje en el cuadro de diálogo '{ Dialog ID }.'. Error en la entrega de la notificación '?<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>' debido al siguiente error en service broker: "No se encuentra el controlador de conversación>"<.

  Nota:

  Puede encontrar el problema durante la fase de recuperación de la base de datos. La recuperación también se ejecuta en una base de datos cuando la base de datos se conecta, se reinicia el servidor, etc.

Causa

Causa del síntoma 1: al especificar NEW_BROKER opción durante la operación de restauración, SQL Server intenta truncar todas las tablas relacionadas con Service Broker. El truncamiento requiere SCH_M bloqueo en el objeto truncado. Por lo tanto, la transacción principal contiene un bloqueo de SCH_M en sysdesend. Cuando se recupera o restaura una base de datos, de forma predeterminada SQL Server intenta activar todas las notificaciones de consulta pendientes, lo que requiere que se inserten filas (mensajes) en la tabla sysdesend. Esta operación requiere un bloqueo de SCH_S en la tabla. Sin embargo, esta operación se produce en una transacción diferente y el intento de adquirir SCH_S bloqueo está bloqueado por el bloqueo de SCH_M que mantiene la primera transacción. Como resultado, el subproceso que ejecuta la restauración ahora está bloqueado en un recurso que posee, situación conocida como interbloqueo automático. El monitor de interbloqueo detecta el interbloqueo y el subproceso finaliza, terminando así la operación de restauración.

Para obtener más información sobre los bloqueos, vea Modos de bloqueo. Los otros síntomas descritos en la sección Síntomas se deben a problemas conocidos que se documentan en los artículos de corrección mencionados en la sección Resolución a continuación.

Solución

Solución alternativa para el síntoma 1: puede solucionar el problema habilitando la marca de seguimiento de nivel de sesión 9109 antes de intentar la operación de restauración. A continuación se muestra un script de ejemplo:

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

Nota:

Una vez restaurada o recuperada por completo la base de datos, se recomienda encarecidamente que compruebe si se están desencadenando notificaciones de consulta. La manera más fácil de lograrlo es cambiar el estado de la base de datos a Solo lectura y volver a cambiarla a Lectura y escritura. Algunas otras maneras de comprobar esto incluyen la desasociación y reacoplamiento de la base de datos, el reinicio SQL Server, etc.

También puede evitar el problema por completo si no especifica si no especifica la opción NEW_BROKER en la operación de restauración y, en su lugar, se usa ALTER DATABASE con NEW_BROKER opción después de restaurar la base de datos.

Para obtener más información, vea DBCC TRACEON - Marcas de seguimiento (Transact-SQL).