次の方法で共有

データベースでクエリ通知が使用されている場合、復元または復旧に失敗したり、時間がかかる場合があります

  • [アーティクル]

この記事は、データベースでクエリ通知が使用されている場合に復元または回復が失敗したり、時間がかかる場合がある問題を解決するのに役立ちます。

元の製品バージョン: SQL Server
元の KB 番号: 2483090

現象

クエリ通知サブスクリプション用に構成されたデータベースでは、次の 1 つ以上の現象が発生することがあります。

  • 現象 1: 復元操作中に NEW_BROKER オプションが指定されている場合、バックアップからのデータベースの復元が失敗し、1205 エラー メッセージが表示されることがあります。 さらに、ダンプ ファイルは SQL Server の Errorlog フォルダーに生成されます。

  • 現象 2: バックアップからデータベースを復元できないと、データベースがオフラインになります。 さらに、次のメッセージが SQL Server エラー ログに記録されます。

    <Datetime> spid61 エラー: 9768、重大度: 16、状態: 1。
    <Datetime> spid61 セキュリティで保護された会話に関連付けられているデータベース ユーザーは、遠くのエンドポイントと資格情報が交換される前に削除されました。 メッセージ交換の作成中は DROP USER の使用を避けてください。
    <Datetime> spid61 データベースを開くときに次のエラーが発生したため、データベース "5" で保留中のクエリ通知を確認できませんでした:"セキュリティで保護された会話に関連付けられているデータベース ユーザーは、遠くのエンドポイントと資格情報が交換される前に削除されました。 メッセージ交換の作成中は DROP USER の使用を避けてください。 クエリ通知サブスクリプションのクリーンアップ操作に失敗しました。 詳細については、前のエラーを参照してください。'.
    <Datetime> spid61 エラー: 9001, 重大度: 16, 状態: 5.
    <Datetime> spid61 データベース 'Test' のログは使用できません。 イベント ログで、関連するエラー メッセージを確認してください。 エラーがある場合は解決し、データベースを再起動してください。
    <Datetime> spid61 エラー: 3314, 重大度: 21, 状態: 4.
    <Datetime> spid61 データベース 'Test' でログに記録された操作を元に戻すと、ログ レコード ID (1835:7401:137) でエラーが発生しました。 通常、この前に特定のエラーが Windows イベント ログ サービスにログ記録されます。 バックアップからデータベースまたはファイルを復元するか、データベースを修復してください。

    Note

    データベースの復旧フェーズ中に問題が発生する可能性があります。 データベースがオンラインになったり、サーバーが再起動されたりすると、データベースでも復旧が実行されます。

  • 現象 3: バックアップからデータベースを復元するには時間がかかる場合があり、次のようなメッセージが SQL Server エラー ログに記録されます。

    日付時刻 SPID クエリ通知の配信で、ダイアログ '{ ダイアログ ID }.' でメッセージを送信できませんでした。 通知 '?< の配信に失敗しました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>' のサービス ブローカーで次のエラーが発生しました: "会話ハンドル "<Conversation Handler>" が見つかりません。'。

    Note

    データベースの復旧フェーズ中に問題が発生する可能性があります。 データベースがオンラインになったり、サーバーが再起動されたりすると、データベースでも復旧が実行されます。

原因

現象 1 の原因: 復元操作中に NEW_BROKER オプションを指定すると、SQL Server は Service Broker 関連のすべてのテーブルを切り捨てようとします。 切り捨てには、切り捨てられたオブジェクトSCH_Mロックが必要です。 したがって、メイン トランザクションは sysdesend のSCH_M ロックを保持します。 データベースが復旧または復元されると、既定では、SQL Server は、sysdesend テーブルに行 (メッセージ) を挿入する必要がある未処理のクエリ通知をすべて起動しようとします。 この操作には、テーブルに対するSCH_Sロックが必要です。 ただし、この操作は別のトランザクションで行われ、SCH_Sロックの取得の試行は、最初のトランザクションによって保持されているSCH_M ロックによってブロックされます。 その結果、復元を実行しているスレッドが所有するリソース (自己デッドロックと呼ばれる状況) でブロックされるようになりました。 デッドロックはデッドロック モニターによって検出され、スレッドは終了され、復元操作が終了します。

ロックの詳細については、「 ロック モード」を参照してください。 「現象」セクションで説明されているその他の現象は、以下の「解決策」セクションに記載されている修正記事に記載されている既知の問題が原因で発生します。

解決方法

現象 1 の回避策: 復元操作を試みる前にセッション レベルのトレース フラグ 9109 を有効にすることで、問題を回避できます。 スクリプトの例を次に示します。

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

データベースが完全に復元または復旧されたら、クエリ通知が発生していることを確認することを強くお勧めします。 これを実現する最も簡単な方法は、データベースの状態を読み取り専用に変更し、読み取り/書き込みに戻す方法です。 これを確認できるその他の方法としては、データベースのデタッチと再アタッチ、SQL Server の再起動などがあります。

また、復元操作で NEW_BROKER オプションを指定せず、代わりにデータベースの復元後に NEW_BROKER オプションでALTER DATABASEを使用することで、問題を完全に回避することもできます。

詳細については、「 DBCC TRACEON - トレース フラグ (Transact-SQL)」を参照してください。