Operations Manager에서 SQL Server Service Broker 문제 해결
Microsoft System Center Operations Manager는 모든 작업 작업을 구현하기 위해 SQL Server Service Broker에 의존합니다. SQL Server Service Broker를 사용하지 않도록 설정하면 모든 작업 작업이 영향을 받습니다. 결과 동작은 시작된 작업에 따라 달라질 수 있습니다. 예를 들어 다음과 같은 문제가 발생할 수 있습니다.
- 백그라운드에서 작업이 완료된 경우에도 검색 마법사가 끝없이 실행되는 것 같습니다.
- 백그라운드에서 작업이 완료된 경우에도 모니터의 상태를 다시 설정하는 작업은 완료되지 않습니다.
이 문서에서는 SQL Server Service Broker 문제에 대한 일반적인 문제 해결 단계를 제공합니다.
참고
이 문서의 SQL 쿼리는 운영 데이터베이스의 기본 이름을 OperationsManager 사용합니다. 를 다른 데이터베이스 이름을 사용하는 경우 를 운영 데이터베이스의 이름으로 바꿉 OperationsManager 있습니다.
SQL Server Service Broker를 사용할 수 있는지 확인
다음 SQL 쿼리를 실행합니다.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'OperationsManager'필드의
is_broker_enabled반환된 값이 1(1)이면 SQL Server Service Broker가 활성화됩니다. 그렇지 않으면 다음 SQL 쿼리를 실행하여 사용하도록 설정합니다.ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE OperationsManager SET ENABLE_BROKER ALTER DATABASE OperationsManager SET MULTI_USER
System Center 데이터 액세스 서비스 다시 시작
SQL Server Service Broker를 사용하도록 설정한 후 System Center OMSDK(데이터 액세스 서비스)를 다시 시작합니다.
SQL Server Management StudioDatabases OperationsManager>>Service Broker로 이동합니다.
큐 및 서비스를 확장합니다.
이름에 다음 값이 포함된 큐 및 서비스가 있는지 확인합니다.
- 큐 및 서비스를 만든 관리 서버의 IP 주소입니다.
- 해당 관리 서버에서 실행되는 OMSDK 서비스(Microsoft.Mom.Sdk.ServiceHost.exe)의 프로세스 ID입니다.
이 예제에서 관리 서버의 IP 주소는 192.168.10.10입니다. OMSDK 서비스의 PID는 3092입니다.
둘 이상의 관리 서버가 있는 경우 각 관리 서버에는 별도의 Service Broker 큐와 서비스가 있습니다.
해당 큐 및 서비스를 찾을 수 없는 경우 OMSDK 서비스를 다시 시작합니다.
여전히 큐 및 서비스를 찾을 수 없는 경우 현재 Service Broker가 손상되었을 수 있습니다. 다음 단계로 이동하여 SQL Server Service Broker를 다시 만듭니다.
SQL Server Service Broker 다시 만들기
다음 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관리 서버에서 OMSDK 서비스를 다시 시작하여 Service Broker 및 큐 및 서비스를 다시 만듭니다. 이 프로세스를 다시 시작하려면 다음 두 가지가 필요할 수 있습니다.
- 첫 번째 다시 시작은 Service Broker를 다시 만듭니다.
- 두 번째 다시 시작은 서비스 큐를 다시 만듭니다.
SQL Server Service Broker가 계속 사용하도록 설정되어 있는지 확인합니다. 사용하지 않도록 설정된 경우 사용하도록 설정합니다.
System Center Data Access Service 다시 시작의 4단계에서 설명한 대로 Service Broker 큐 및 서비스가 생성되었는지 확인합니다.
고급 문제 해결 방법
이전 단계에서 문제를 resolve 않는 경우 Service Broker 이벤트를 포함하는 SQL Server Profiler 추적을 수집합니다.
OMSDK 서비스가 서비스를 다시 시작할 때 서비스 및 큐를 만드는 경우 샘플 추적:
모니터의 상태를 다시 설정할 때의 샘플 추적:
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;