USE [master]
GO
CREATE PROCEDURE [dbo].[usp_CaptureBlockingLocking] AS
SET NOCOUNT ON ;

IF EXISTS
(
	SELECT *
	FROM
	sys.dm_tran_locks dtl 
	INNER JOIN
	sys.dm_os_waiting_tasks dowt
		ON dtl.lock_owner_address = dowt.resource_address
)

BEGIN
	DECLARE @RunID SMALLINT
	SELECT @RunID = ISNULL(MAX(RunID),0) + 1 FROM RCSHJSQLTEST.xxDBAxx.dbo.dbaLockInfo
	
	--Blocking Information
	INSERT INTO RCSHJSQLTEST.xxDBAxx.dbo.dbaBlockInfo
	(
		RunID,ResourceEntityID,ResourceDescription,IndexName,RequestLockType,RequestLockMode
		,WaitingSPID,WaitingTimeMins,WaitingStatement,BlockerSPID,BlockerStatement
	)
	SELECT
	RunID = @RunID
	,ResourceEntityID =
		CASE
			WHEN resource_type = 'object' THEN OBJECT_NAME(dtl.resource_associated_entity_id)
			WHEN resource_type = 'database' THEN DB_NAME(dtl.resource_associated_entity_id)
			WHEN resource_type IN ('key','page') THEN OBJECT_NAME(p.[object_id])
--				(
--					SELECT OBJECT_NAME(OBJECT_ID) FROM sys.partitions p WHERE p.hobt_id = dtl.resource_associated_entity_id
--				)
			ELSE CAST(dtl.resource_associated_entity_id AS VARCHAR(100))
		END
	,ResourceDescription = dtl.resource_description
	,IndexName = i.[name]
	,RequestLockType = dtl.resource_type
	,RequestLockMode =
		CASE dtl.request_mode
			WHEN 'S' THEN 'Shared'
			WHEN 'U' THEN 'Update'
			WHEN 'X' THEN 'Exclusive'
			WHEN 'IS' THEN 'IntentShared'
			WHEN 'IX' THEN 'IntentExclusive'
			WHEN 'IU' THEN 'IntentUpdate'
			WHEN 'SIX' THEN 'SharedWithIntentExclusive'
			WHEN 'SIU' THEN 'SharedIntentUpdate'
			WHEN 'UIX' THEN 'UpdateIntentExclusive'
			WHEN 'Sch-M' THEN 'SchemaModification'
			WHEN 'Sch-S' THEN 'SchemaStability'
			WHEN 'BU' THEN 'BulkUpdate'
			ELSE dtl.request_mode
		END
	,WaitingSPID = dtl.request_session_id
	,WaitingTimeMins = dowt.wait_duration_ms / 60000
	--,WaitingBatch =
	--(
	--	SELECT [text] FROM sys.dm_exec_requests AS r
	--	CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
	--	WHERE r.session_id = dtl.request_session_id
	--)
	,WaitingStatement =
	LEFT
	(
		(
			SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
				(
					CASE
						WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.[text])) * 2
						ELSE r.statement_end_offset
					END - r.statement_start_offset)/2
				) 
			FROM sys.dm_exec_requests r
			CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) qt
			WHERE r.session_id = dtl.request_session_id
		)
	,300
	)
	,BlockerSPID = dowt.blocking_session_id
	,BlockerStatement =
	LEFT
	(
		(
			SELECT [text] FROM sys.sysprocesses p
			CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
			WHERE p.spid = dowt.blocking_session_id
		)
	,300
	)
	FROM
	sys.dm_tran_locks dtl 
	INNER JOIN
	sys.dm_os_waiting_tasks dowt
		ON dtl.lock_owner_address = dowt.resource_address
	LEFT JOIN
	sys.partitions p
		ON dtl.resource_associated_entity_id = p.hobt_id
	LEFT JOIN
	sys.indexes i
		ON p.[object_id] = i.[object_id]
			AND p.index_id = i.index_id ;


	--Locking Information
	INSERT INTO RCSHJSQLTEST.xxDBAxx.dbo.dbaLockInfo
	(
		RunID,LoginName,IsolationLevel,RequestSPID,ResourceType,ResourceSubType
		,RequestMode,RequestType,RequestStatus,RequestOwnerType,ResourceEntityID
	)
	SELECT
	RunID = @RunID
	,LoginName = des.login_name
	,IsolationLevel =
		CASE des.transaction_isolation_level
			WHEN 0 THEN 'Unspecified'
			WHEN 1 THEN 'ReadUncomitted'
			WHEN 2 THEN 'ReadCommitted'
			WHEN 3 THEN 'Repeatable'
			WHEN 4 THEN 'Serializable'
			WHEN 5 THEN 'Snapshot'
	END
	,RequestSPID = dtl.request_session_id
	,ResourceType = dtl.resource_type
	,ResourceSubType = dtl.resource_subtype
	,RequestMode =
		CASE dtl.request_mode
			WHEN 'S' THEN 'Shared'
			WHEN 'U' THEN 'Update'
			WHEN 'X' THEN 'Exclusive'
			WHEN 'IS' THEN 'IntentShared'
			WHEN 'IX' THEN 'IntentExclusive'
			WHEN 'IU' THEN 'IntentUpdate'
			WHEN 'SIX' THEN 'SharedWithIntentExclusive'
			WHEN 'SIU' THEN 'SharedIntentUpdate'
			WHEN 'UIX' THEN 'UpdateIntentExclusive'
			WHEN 'Sch-M' THEN 'SchemaModification'
			WHEN 'Sch-S' THEN 'SchemaStability'
			WHEN 'BU' THEN 'BulkUpdate'
			ELSE dtl.request_mode
		END
	,RequestType = dtl.request_type
	,RequestStatus = dtl.request_status
	,RequestOwnerType = dtl.request_owner_type
	,ResourceEntityID =
		CASE
			WHEN resource_type = 'object' THEN OBJECT_NAME(resource_associated_entity_id)
			WHEN resource_type = 'database' THEN DB_NAME(resource_associated_entity_id)
			WHEN resource_type IN ('key','page') THEN
				(
					SELECT OBJECT_NAME(p.[object_id]) FROM sys.partitions p WHERE p.hobt_id = dtl.resource_associated_entity_id
				)
			ELSE CAST(resource_associated_entity_id AS VARCHAR(100))
		END
	FROM
	sys.dm_tran_locks dtl
	LEFT JOIN
	sys.dm_exec_sessions des
		ON dtl.request_session_id = des.session_id
	WHERE
	dtl.request_session_id <> @@spid
	AND dtl.request_status = 'WAIT'
END
ELSE
RETURN