Partager via

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

  • Article

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 la notification de requête est utilisée dans une base de données.

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

Symptômes

Vous remarquerez peut-être 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 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 consignés dans le journal des erreurs SQL Server :

    <Erreur spid61 Datetime> : 9768, Gravité : 16, État : 1.
    <Datetime> spid61 Un utilisateur de base de données associé à la conversation sécurisée a été supprimé avant que les informations d’identification n’avaient été échangées avec le point de terminaison lointain. Évitez d'utiliser DROP USER pendant la création de conversations.
    <Datetime> spid61 Échec de la vérification des 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’avaient été échangées avec le point de terminaison lointain. Évitez d'utiliser DROP USER pendant la création de conversations. Échec de l'opération de nettoyage des abonnements aux notifications de requête. Pour plus d’informations, consultez les erreurs précédentes.
    <Erreur spid61 Datetime> : 9001, Gravité : 16, État : 5.
    <Datetime> spid61 Le journal de la base de données « Test » n’est pas disponible. Consultez le journal des événements pour voir s'il contient des messages d'erreur liés à ce problème. Résolvez toutes les erreurs et redémarrez la base de données.
    <Erreur spid61 Datetime> : 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 lors de l’ID d’enregistrement du journal (1835:7401:137). En général, l'erreur spécifique est déjà journalisée dans le service de journal d'événements Windows. Restaurez la base de données ou le fichier à partir d'une sauvegarde, ou réparez la base de données.

    Note

    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 les messages similaires aux suivants sont enregistrés dans le journal des erreurs SQL Server :

    La remise des notifications de requête SPID de date 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=" » id="2881 » type="change » source="database » info="restart » database_id="7 » sid="https://schemas.microsoft.com/SQL/Notifications/QueryNotification0x010500000000000515000000FA48F22A6990BA52422C73DFF9030000"><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.'

    Note

    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 principale contient 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 autre transaction 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 qu’elle possède, situation connue sous le nom d’interblocage automatique. L’interblocage est détecté par le moniteur d’interblocage et le thread est arrêté, ce qui termine l’opération de restauration.

Pour plus d’informations sur les verrous, consultez Modes de verrouillage. Les autres symptômes abordés dans la section Symptômes sont causés par 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. Un exemple de script est illustré ci-dessous :

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

Note

Une fois la base de données entièrement restaurée ou récupérée, il est vivement recommandé de vérifier que les notifications de requête sont déclenchées. Le moyen le plus simple d’y parvenir consiste à modifier l’état de la base de données en lecture seule et à revenir en lecture-écriture. D’autres méthodes vous permettent de vérifier cela : détacher et détacher la base de données, redémarrer SQL Server, etc.

Vous pouvez également éviter 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 l’option NEW_BROKER une fois la base de données restaurée.

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