La restauration ou la récupération peut échouer ou prendre beaucoup de temps si une notification de requête est utilisée dans une base de données

Cet article vous aide à résoudre le problème où la restauration ou la récupération peut échouer ou prendre beaucoup de temps si une notification de requête est utilisée dans une base de données.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 2483090

Symptômes

Vous pouvez remarquer un ou plusieurs des symptômes suivants avec une base de données configurée pour les abonnements aux notifications de requête :

• Symptôme 1 : la restauration de la base de données à partir de sa sauvegarde peut échouer avec le message d’erreur 1205 si NEW_BROKER option est spécifiée pendant l’opération de restauration. En outre, les fichiers de vidage sont générés dans le dossier Errorlog de l’SQL Server.

• Symptôme 2 : la restauration de la base de données à partir de sa sauvegarde échoue et la base de données est hors connexion. En outre, les messages suivants sont enregistrés dans le journal des erreurs SQL Server :

  <Datetime> spid61 Erreur : 9768, Gravité : 16, État : 1.
  <Datetime> spid61 Un utilisateur de base de données associé à la conversation sécurisée a été supprimé avant l’échange des informations d’identification avec le point de terminaison éloigné. Évitez d’utiliser DROP USER pendant la création de conversations.
  <DateTime> spid61 Impossible de case activée pour les notifications de requête en attente dans la base de données « 5 » en raison de l’erreur suivante lors de l’ouverture de la base de données : « Un utilisateur de base de données associé à la conversation sécurisée a été supprimé avant que les informations d’identification n’ont été échangées avec le point de terminaison éloigné. Évitez d’utiliser DROP USER pendant la création de conversations. L’opération de nettoyage des abonnements aux notifications de requête a échoué. Pour plus d’informations, consultez les erreurs précédentes.
  <Datetime> spid61 Erreur : 9001, Gravité : 16, État : 5.
  <Datetime> spid61 Le journal de la base de données « Test » n’est pas disponible. Vérifiez les messages d’erreur associés dans le journal des événements. Résolvez les erreurs et redémarrez la base de données.
  <Datetime> spid61 Erreur : 3314, Gravité : 21, État : 4.
  <Datetime> spid61 Lors de l’annulation d’une opération journalisée dans la base de données « Test », une erreur s’est produite au niveau de l’ID d’enregistrement du journal (1835 :7401 :137). En règle générale, l’échec spécifique est enregistré précédemment en tant qu’erreur dans le service Journal des événements Windows. Restaurez la base de données ou le fichier à partir d’une sauvegarde, ou réparez la base de données.

  Remarque

  Vous pouvez rencontrer le problème pendant la phase de récupération de la base de données. La récupération est également exécutée sur une base de données lorsque la base de données est mise en ligne, que le serveur est redémarré, etc.

• Symptôme 3 : La restauration de la base de données à partir de sa sauvegarde peut prendre beaucoup de temps et des messages similaires à ce qui suit sont enregistrés SQL Server journal des erreurs :

  Date Time SPID La remise de notification de requête SPID n’a pas pu envoyer de message dans la boîte de dialogue « { ID de boîte de dialogue } ». La remise a échoué pour la notification ' ?<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> » en raison de l’erreur suivante dans Service Broker : « Le handle de conversation «< Gestionnaire> de conversation » est introuvable.

  Remarque

  Vous pouvez rencontrer le problème pendant la phase de récupération de la base de données. La récupération est également exécutée sur une base de données lorsque la base de données est mise en ligne, que le serveur est redémarré, etc.

Cause

Cause du symptôme 1 : lorsque vous spécifiez NEW_BROKER option pendant l’opération de restauration, SQL Server tente de tronquer toutes les tables associées à Service Broker. La troncation nécessite SCH_M verrou sur l’objet tronqué. La transaction main détient donc un verrou SCH_M sur sysdesend. Lorsqu’une base de données est récupérée ou restaurée, par défaut, SQL Server tente de déclencher toutes les notifications de requête en attente, ce qui nécessite l’insertion de lignes (messages) dans la table sysdesend. Cette opération nécessite un verrou SCH_S sur la table. Toutefois, cette opération se produit sur une transaction différente et la tentative d’acquisition de SCH_S verrou est bloquée par le verrou SCH_M détenu par la première transaction. Par conséquent, le thread qui exécute la restauration est désormais bloqué sur une ressource dont il est propriétaire, situation appelée interblocage automatique. L’interblocage est détecté par le moniteur d’interblocage et le thread est arrêté, ce qui met fin à l’opération de restauration.

Pour plus d’informations sur les verrous, consultez Modes de verrouillage. Les autres symptômes décrits dans la section Symptômes sont dus à des problèmes connus documentés dans les articles de résolution mentionnés dans la section Résolution ci-dessous.

Résolution

Solution de contournement pour le symptôme 1 : vous pouvez contourner le problème en activant l’indicateur de trace au niveau de la session 9109 avant de tenter l’opération de restauration. Voici un exemple de script :

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

Remarque

Une fois la base de données entièrement restaurée ou récupérée, il est vivement recommandé de case activée pour vous assurer que les notifications de requête sont déclenchées. Le moyen le plus simple d’y parvenir consiste à modifier la status de la base de données en lecture seule et à la réajuster en lecture-écriture. Vous pouvez également case activée pour cela, notamment le détachement et le rattachement de la base de données, le redémarrage SQL Server, etc.

Vous pouvez également éviter complètement le problème en ne spécifiant pas en ne spécifiant pas l’option NEW_BROKER sur l’opération de restauration et en utilisant ALTER DATABASE à la place avec NEW_BROKER option après la restauration de la base de données.

Pour plus d’informations, consultez DBCC TRACEON - Indicateurs de trace (Transact-SQL).