Service Broker에서 수행하는 작업 모니터링(sp_broker_works)

서비스브로커의 Conversion 내에서 SP와 같은 작업을 실행하는 경우, Waitfor 등의 요인으로 인해 SQL Server가 중지되지 않는 경우가 있다.

이때, 아래의 SP를 사용한다.

--해당 SP는 필요에 따라 계속 수정하겠습니다.

USE master

GO

IF OBJECT_ID('dbo.sp_broker_works') IS NULL

EXEC ('CREATE PROCEDURE dbo.sp_broker_works AS SELECT 1')

GO

/*2015-02-02 SQLDBWook@gmail.com / SQL Server 2012*/

ALTER PROCEDURE dbo.sp_broker_works

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

BEGIN

SELECT BAT.spid AS BROKER_REQUEST_SESSION_ID

, ER.start_time AS BROKER_REQUEST_START_TIME

, DB_NAME(BAT.database_id) AS BROKER_REQUEST_DATABASE_NAME

, BAT.procedure_name AS RUNNING_PARENTS_PROCEDURE_NAME

, BAT.execute_as AS EXECUTOR_DATABASE_USER_ID

, DB_NAME(EST.dbid) AS RUNNING_CHILD_PROCEDURE_DATABASE_NAME

, OBJECT_NAME(EST.objectid,EST.dbid) AS RUNNING_CHILD_PROCEDURE_NAME

, OWT.wait_type AS CHILD_PROCEDURE_WAIT_TYPE

, ER.wait_time/1000 AS CHILD_PROCEDURE_WAIT_TIME_SEC

 FROM sys.dm_broker_activated_tasks AS BAT

 JOIN sys.dm_exec_requests AS ER

ON BAT.spid = ER.session_id

 LEFT JOIN sys.dm_os_waiting_tasks AS OWT

ON OWT.session_id = ER.session_id

OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) AS EST

END

GO

EXEC sys.sp_MS_marksystemobject 'sp_broker_works'

GO

EXEC sp_broker_works