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

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

元の製品バージョン: 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 イベント ログ サービスでエラーとして記録されます。 バックアップからデータベースまたはファイルを復元するか、データベースを修復します。

  注:

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

• 現象 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>' は、サービス ブローカーで次のエラーが発生したためです。<>

  注:

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

原因

現象 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

注:

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

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

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