次の方法で共有


Operations Manager SQL Server Service Broker の問題のトラブルシューティング

Microsoft System Center Operations Manager は、Service Broker SQL Server依存して、すべてのタスク操作を実装します。 SQL Server Service Broker が無効になっている場合、すべてのタスク操作が影響を受けます。 結果の動作は、開始されたタスクによって異なる場合があります。 たとえば、次の問題が発生する可能性があります。

  • 検出ウィザードは、バックグラウンドのタスクが完了したにもかかわらず、無限に実行されているようです。
  • バックグラウンドのタスクが完了しても、モニターの正常性のリセットは完了しません。

この記事では、Service Broker の問題に関する一般的なトラブルシューティング手順SQL Server説明します。

注:

この記事の SQL クエリでは、オペレーショナル データベースの既定の OperationsManager 名前を使用します。 別のデータベース名を使用する場合は、操作データベースの名前に置き換えます OperationsManager

Service Broker SQL Server有効になっているかどうかを確認する

  1. 次の SQL クエリを実行します。

    SELECT is_broker_enabled FROM sys.databases WHERE name = 'OperationsManager'
    
  2. フィールドの戻り値is_broker_enabled1 (1) の場合、Service Broker SQL Server有効になります。 それ以外の場合は、次の SQL クエリを実行して有効にします。

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
    ALTER DATABASE OperationsManager SET MULTI_USER
    

System Center Data Access Service を再起動する

  1. Service Broker SQL Server有効にした後、System Center Data Access Service (OMSDK) を再起動します。

  2. SQL Server Management Studioで、[データベース] [OperationsManager>Service Broker] に移動します>。

  3. [ キューサービス] を展開します。

  4. キューとサービスの名前に次の値が含まれていることを確認します。

    • キューとサービスを作成した管理サーバーの IP アドレス。
    • その管理サーバーで実行されている OMSDK サービス (Microsoft.Mom.Sdk.ServiceHost.exe) のプロセス ID。

    その管理サーバーで実行されている OMSDK サービスのプロセス ID を確認します。

    この例では、管理サーバーの IP アドレスは 192.168.10.10 です。 OMSDK サービスの PID は 3092 です。

    OMSDK サービスの PID のスクリーンショット。

    複数の管理サーバーがある場合、各管理サーバーには個別の Service Broker キューとサービスがあります。

  5. 対応するキューとサービスが見つからない場合は、OMSDK サービスをもう一度再起動します。

それでもキューとサービスが見つからない場合は、現在の Service Broker が破損している可能性があります。 次の手順に進み、SQL Server Service Broker を再作成します。

SQL Server Service Broker を再作成する

  1. 次の SQL クエリを順番に実行します。

    declare @i int 
    set @i=0
    DECLARE @handle UNIQUEIDENTIFIER 
    declare @service_id int
    declare @service_name nvarchar (100)
    declare @far_service nvarchar (70)
    DECLARE conv_cur CURSOR FAST_FORWARD FOR 
    SELECT CONVERSATION_HANDLE, service_id, far_service 
    FROM SYS.CONVERSATION_ENDPOINTS 
    OPEN conv_cur; 
    FETCH NEXT FROM conv_cur INTO @handle, @service_id, @far_service; 
    while (@@FETCH_STATUS = 0 and (@i<500000))
    BEGIN
    select top 1 @service_name=name from sys.services where service_id=@service_id
    begin
    END CONVERSATION @handle WITH CLEANUP 
    end
    FETCH NEXT FROM conv_cur INTO @handle, @service_id, @far_service;
    set @i=@i+1
    END 
    CLOSE conv_cur
    DEALLOCATE conv_cur
    go
    
    declare @servicename sysname
    declare @queuename sysname
    declare @cmd nvarchar(200)
    declare @serverqid nvarchar(25)
    set @serverqid='%Queue_mid%'
    while ((select count(*) from sys.service_queues WHERE name like @serverqid )>1)
    begin
    set @servicename= (select top 1 s.name from sys.service_queues as q join sys.services as s on q.object_id=s.service_queue_id WHERE q.name like @serverqid order by q.create_date asc)
    set @cmd= 'DROP SERVICE '+@servicename
    exec sp_sqlexec @cmd
    set @queuename= (select top 1 Object_name(object_id) from sys.service_queues WHERE name like @serverqid order by create_date asc)
    set @cmd= 'DROP QUEUE '+@queuename
    exec sp_sqlexec @cmd
    end
    go
    
    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET NEW_BROKER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET MULTI_USER
    go
    
    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
    ALTER DATABASE OperationsManager SET MULTI_USER
    go
    
  2. 管理サーバー上の OMSDK サービスを再起動して、Service Broker とキューとサービスを再作成します。 このプロセスでは、次の 2 つの再起動が必要になる場合があります。

    • 最初の再起動では、Service Broker が再作成されます
    • 2 回目の再起動では、サービス キューが再作成されます
  3. SQL Server Service Broker がまだ有効になっていることを確認します。 無効になっている場合は、有効にします。

  4. 「System Center Data Access Service を再起動する」の手順 4 で説明されているように、Service Broker キューとサービスが生成されていることを確認します。

高度なトラブルシューティング

前の手順で問題が解決しない場合は、Service Broker イベントを含むSQL Server Profiler トレースを収集します。

SQL Server Profilerトレースの収集のスクリーンショット。

  • OMSDK サービスが再起動されたときにサービスとキューを作成するときのサンプル トレース:

    キューとサービスを作成するときのサンプル トレース。

  • モニターの正常性をリセットするときのサンプル トレース:

    モニターの正常性をリセットするときのサンプル トレース。

  • Service Broker が無効になっている場合のサンプル トレース:

    Service Broker が無効になっている場合のサンプル トレース。

さらに、運用データベースに対して次の SQL スクリプトを実行して、診断ログを収集します。

USE master
go

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON;
DECLARE @StartTime datetime
select @@version as 'Version'
select GETDATE() as 'RunDateTime', GETUTCDATE() as 'RunUTCDateTime', SYSDATETIMEOFFSET() as 'SysDateTimeOffset'

select @@servername as 'ServerName'
PRINT '-- sys.databases --' 
select * from master.sys.databases where is_broker_enabled = 1 and name not in('tempdb', 'model', 'AdventureWorks', 'AdventureWorksDW')
PRINT ''

PRINT '-- sys.dm_broker_activated_tasks --' 
select * from sys.dm_broker_activated_tasks
PRINT ''

PRINT '-- sys.dm_broker_connections --' 
select * from sys.dm_broker_connections
PRINT ''
PRINT '-- COUNT Broker Connections --'
SELECT count(*) as Cnt, state_desc, login_state_desc from sys.dm_broker_connections GROUP BY state_desc, login_state_desc ORDER BY state_desc 
PRINT ''

PRINT '-- sys.dm_broker_forwarded_messages --' 
select * from sys.dm_broker_forwarded_messages
PRINT ''

PRINT '-- sys.service_broker_endpoints --' 
select * from sys.service_broker_endpoints
PRINT ''

PRINT '-- sys.tcp_endpoints --' 
select * from sys.tcp_endpoints
PRINT ''

PRINT '-- sys.certificates --' 
select * from sys.certificates
PRINT ''

PRINT '-- sys.database_mirroring --' 
select * from sys.database_mirroring where mirroring_guid is not null
PRINT ''

PRINT '-- sys.dm_db_mirroring_connections --' 
select * from sys.dm_db_mirroring_connections
PRINT ''

PRINT '-- sys.dm_os_memory_clerks (broker) --'  
select * from sys.dm_os_memory_clerks where type like '%BROKER%' order by type desc

-- Loop Through DBs and Gather SSB information specific to each DB
DECLARE tnames_cursor CURSOR
FOR SELECT name 
   FROM master.sys.databases 
   WHERE is_broker_enabled = 1 
   and state = 0 
   and name not in('tempdb', 'model', 'AdventureWorks', 'AdventureWorksDW')
   ORDER BY [name]
OPEN tnames_cursor;
DECLARE @dbname sysname;
DECLARE @SCI int; -- Checking for Broker activity
DECLARE @cmd3 nvarchar(1024); -- New Command
FETCH NEXT FROM tnames_cursor INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   SELECT @SCI = 0; -- service_contract_id
   select @dbname = RTRIM(@dbname);
   EXEC ('USE [' + @dbname + ']');
   SELECT @cmd3 = N'SELECT @SCI_OUT = MAX(service_contract_id) FROM ' + @dbname + '.sys.service_contracts';
   EXEC sp_executesql @cmd3, N'@SCI_OUT INT OUTPUT', @SCI_OUT = @SCI OUTPUT; 
   IF @SCI > 7
      BEGIN
      PRINT ''
      PRINT '====================================================================================='
      PRINT 'Begin Database: ' + @dbname
      SELECT @StartTime = GETDATE()
      PRINT 'Start Time : ' + CONVERT(Varchar(50), @StartTime)
    
      PRINT ''
      PRINT '-- sys.service_message_types --'
      EXEC ('SELECT  * FROM ' + @dbname + '.sys.service_message_types');
      
      -- PRINT ''
      -- PRINT '-- sys.service_contract_message_usages --' 
      -- EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contract_message_usages');
      
      PRINT ''
      PRINT '-- sys.service_contracts --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contracts');
      
      -- PRINT ''
      -- print '-- sys.service_contract_usages --' 
      -- EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contract_usages');
      
      PRINT ''
      PRINT '-- sys.service_queues --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.service_queues');
      
      -- PRINT ''
      -- PRINT '-- sys.service_queue_usages --' 
      -- EXEC ('SELECT * FROM ' + @dbname + '.sys.service_queue_usages');
      
      PRINT ''
      PRINT '-- sys.services --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.services');
      
      PRINT ''
      PRINT '-- sys.routes --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.routes');
      
      PRINT ''
      PRINT '-- sys.remote_service_bindings --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.remote_service_bindings');
      
      PRINT ''
      PRINT '-- sys.certificates --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.certificates');
      
      PRINT ''
      PRINT '-- sys.dm_qn_subscriptions --' 
      EXEC ('SELECT * FROM ' + @dbname + '.sys.dm_qn_subscriptions');
      
      PRINT '-- sys.dm_broker_queue_monitors, current state, last activation, current backlog in transmission queue --' 
      EXEC ('USE ' + @dbname + ';SELECT t1.name AS [Service_Name],  t3.name AS [Schema_Name],  t2.name AS [Queue_Name],  
      CASE WHEN t4.state IS NULL THEN ''Not available'' 
      ELSE t4.state 
      END AS [Queue_State],  
      CASE WHEN t4.tasks_waiting IS NULL THEN ''--'' 
      ELSE CONVERT(VARCHAR, t4.tasks_waiting) 
      END AS tasks_waiting, 
      CASE WHEN t4.last_activated_time IS NULL THEN ''--'' 
      ELSE CONVERT(varchar, t4.last_activated_time) 
      END AS last_activated_time ,  
      CASE WHEN t4.last_empty_rowset_time IS NULL THEN ''--'' 
      ELSE CONVERT(varchar,t4.last_empty_rowset_time) 
      END AS last_empty_rowset_time, 
      ( 
         SELECT COUNT(*) 
         FROM sys.transmission_queue t6 WITH (NOLOCK)
         WHERE (t6.from_service_name = t1.name) 
      ) AS [Tran_Message_Count],
      DB_NAME() AS DB_NAME 
      FROM sys.services t1 WITH (NOLOCK) INNER JOIN sys.service_queues t2 WITH (NOLOCK)
      ON ( t1.service_queue_id = t2.object_id )   
      INNER JOIN sys.schemas t3 WITH (NOLOCK) ON ( t2.schema_id = t3.schema_id )  
      LEFT OUTER JOIN sys.dm_broker_queue_monitors t4 WITH (NOLOCK)
      ON ( t2.object_id = t4.queue_id  AND t4.database_id = DB_ID() )  
      INNER JOIN sys.databases t5 WITH (NOLOCK) ON ( t5.database_id = DB_ID() );')
      PRINT ''
      

      PRINT ''
      PRINT 'sys.transmission_queue (toal count, group count, and top 500)'

      -- Using count against MetaData columns rather than COUNT(*) because it is faster, and we don't need exact counts
      PRINT '-- TOTAL COUNT sys.transmission_queue --' 
      EXEC ('SELECT p.rows as TQ_Count FROM ' + @dbname + '.sys.objects as o join ' + @dbname + '.sys.partitions as p on p.object_id = o.object_id where o.name = ''sysxmitqueue''')
      -- EXEC ('SELECT count(*) as TQ_Count FROM ' + @dbname + '.sys.transmission_queue with (nolock)');  -- more accurate count

      PRINT ''
      PRINT '-- GROUP COUNT sys.transmission_queue --'
      SELECT COUNT(*) as TQ_GroupCnt, transmission_status FROM sys.transmission_queue GROUP BY transmission_status
      
      PRINT ''
      PRINT 'TOP 500'
      print '-- sys.transmission_queue --' 
      EXEC ('USE ' + @dbname + ';SELECT top 500 conversation_handle, to_service_name, to_broker_instance, from_service_name, 
         service_contract_name, enqueue_time, message_sequence_number, message_type_name, is_conversation_error, 
         is_end_of_dialog, priority, transmission_status, DB_NAME() as DB_Name FROM ' + @dbname + '.sys.transmission_queue with (nolock) order by enqueue_time, message_sequence_number');
      
      PRINT ''
      print 'sys.conversation_endpoints (total count, group count, and top 500)'
      -- Using count against MetaData columns rather than COUNT(*) becuase it is faster, and we don't need exact counts
      PRINT '-- TOTAL COUNT sys.conversation_endpoints --'
      EXEC ('SELECT p.rows as CE_Count FROM ' + @dbname + '.sys.objects as o join ' + @dbname + '.sys.partitions as p on p.object_id = o.object_id  where o.name = ''sysdesend''')
      -- EXEC ('SELECT count(*) as count FROM ' + @dbname + '.sys.conversation_endpoints with (nolock)');
      
      PRINT ''
      PRINT '-- GROUP COUNT sys.conversation_endpoints --'
      EXEC  ('SELECT COUNT(*) as CE_GroupCnt, state_desc FROM ' + @dbname + '. sys.conversation_endpoints GROUP BY state_desc')
      
      PRINT ''
      PRINT 'TOP 500'
      PRINT '-- sys.conversation_endpoints --'
      EXEC ('USE ' + @dbname + ';SELECT top 500 *, DB_NAME() as DB_Name FROM ' + @dbname + '.sys.conversation_endpoints with (nolock)');
      
    -- Gather Activation Proc Code
    /*
      SET QUOTED_IDENTIFIER OFF;
      DECLARE @cmd nvarchar(1024)
      DECLARE @cmd2 nvarchar(1024)
      select @cmd = 'DECLARE tproc_cursor CURSOR FOR select activation_procedure from ' + @dbname + '.sys.service_queues where activation_procedure is not null'
      EXEC (@cmd)
      OPEN tproc_cursor;
      DECLARE @proc sysname;
      DECLARE @len int
      FETCH NEXT FROM tproc_cursor INTO @proc;
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
         select @proc = rtrim(@proc)
         select @len = len(@proc) - 8;
         select @proc = substring(@proc, 8, @len)
         select @proc
         EXEC ("select definition from " + @dbname + ".sys.sql_modules where definition like '%" + @proc + "%'")
         FETCH NEXT FROM tproc_cursor INTO @proc;
      END;
      CLOSE tproc_cursor;
      DEALLOCATE tproc_cursor;
      SET QUOTED_IDENTIFIER ON;
   */
   
   PRINT ''
   PRINT 'End of Database: ' + @dbname 
   PRINT 'END Time : ' + CONVERT(Varchar(50), GetDate())
   PRINT 'Data Collection Duration in milliseconds for ' + @dbname
   PRINT ''
   SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) as Duration_ms

   PRINT '====================================================================================='
   PRINT '====================================================================================='
   PRINT '' 
   END;
   FETCH NEXT FROM tnames_cursor INTO @dbname;
END;
CLOSE tnames_cursor;
DEALLOCATE tnames_cursor;

関連情報