These queries create a temporary table and then every X minutes (set below) it inserts into that table information about any locked records in the SQL Database.

Small. No user data is modified by this query.

Run this SQL one time to create the temp table.

CREATE TABLE [dbo].[Temp_BlockingItems](
       [DTRun] [datetime] NOT NULL,
       [WaitingSessionID] [SMALLINT] NULL,
       [BlockingSessionID] [SMALLINT] NULL,
       [WaitingUserSessionLogin] [nvarchar](128) NOT NULL,
       [BlockingUserSessionLogin] [nvarchar](128) NOT NULL,
       [WaitingUserConnectionLogin] [nvarchar](128) NOT NULL,
       [BlockingSessionConnectionLogin] [nvarchar](128) NOT NULL,
       [WaitDuration] [BIGINT] NULL,
       [WaitType] [nvarchar](60) NULL,
       [WaitRequestMode] [nvarchar](60) NOT NULL,
       [WaitingProcessStatus] [nvarchar](30) NULL,
       [BlockingSessionStatus] [nvarchar](30) NULL,
       [WaitResource] [nvarchar](256) NOT NULL,
       [WaitResourceType] [nvarchar](60) NOT NULL,
       [WaitResourceDatabaseID] [INT] NOT NULL,
       [WaitResourceDatabaseName] [nvarchar](128) NULL,
       [WaitResourceDescription] [nvarchar](2048) NULL,
       [WaitingSessionProgramName] [nvarchar](128) NULL,
       [BlockingSessionProgramName] [nvarchar](128) NULL,
       [WaitingHost] [nvarchar](128) NULL,
       [BlockingHost] [nvarchar](128) NULL,
       [WaitingCommandType] [nvarchar](16) NOT NULL,
       [WaitingCommandText] [nvarchar](MAX) NULL,
       [WaitingCommandRowCount] [BIGINT] NOT NULL,
       [WaitingCommandPercentComplete] [REAL] NOT NULL,
       [WaitingCommandCPUTime] [INT] NOT NULL,
       [WaitingCommandTotalElapsedTime] [INT] NOT NULL,
       [WaitingCommandReads] [BIGINT] NOT NULL,
       [WaitingCommandWrites] [BIGINT] NOT NULL,
       [WaitingCommandLogicalReads] [BIGINT] NOT NULL,
       [WaitingCommandQueryPlan] [xml] NULL,
       [WaitingCommandPlanHandle] [varbinary](64) NULL,
       [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Temp_BlockingItems] PRIMARY KEY CLUSTERED
(
       [ID] ASC
)
)
;
CREATE NONCLUSTERED INDEX [IX_Temp_BlockingItems_DT] ON [dbo].[Temp_BlockingItems]
(
       [DTRun] ASC
)

Run this SQL to create the log entries.

Notes:

• This will run every 3 minutes for 6 hours.
• You must set the time-out in SQL server to longer than the run time. (This is under Tools | Options | Designers | Transaction Time-Out.)
• SQL must be set to compatibility mode of 2005 or greater or your will get a syntax error in the query. (To set/check this, right-click on the database, choose Properties | Options Tab | Compatibility Mode.)

INSERT INTO Temp_BlockingItems
SELECT 
   	    GetDate() AS DTRun
	    , b.[session_id] AS [WaitingSessionID]
		,b.[blocking_session_id] AS [BlockingSessionID]
		,b.[login_name] AS [WaitingUserSessionLogin]
		,s1.[login_name] AS [BlockingUserSessionLogin]
		,b.[original_login_name] AS [WaitingUserConnectionLogin] 
		,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
		,b.[wait_duration_ms] AS [WaitDuration]
		,b.[wait_type] AS [WaitType]
		,t.[request_mode] AS [WaitRequestMode]
		,UPPER(b.[STATUS]) AS [WaitingProcessStatus]
		,UPPER(s1.[STATUS]) AS [BlockingSessionStatus]
		,b.[wait_resource] AS [WaitResource]
		,t.[resource_type] AS [WaitResourceType]
		,t.[resource_database_id] AS [WaitResourceDatabaseID]
		,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
		,b.[resource_description] AS [WaitResourceDescription]
		,b.[program_name] AS [WaitingSessionProgramName]
		,s1.[program_name] AS [BlockingSessionProgramName]
		,b.[host_name] AS [WaitingHost]
		,s1.[host_name] AS [BlockingHost]
		,b.[command] AS [WaitingCommandType]
		,b.[text] AS [WaitingCommandText]
		,b.[ROW_COUNT] AS [WaitingCommandRowCount]
		,b.[percent_complete] AS [WaitingCommandPercentComplete]
		,b.[cpu_time] AS [WaitingCommandCPUTime]
		,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
		,b.[reads] AS [WaitingCommandReads]
		,b.[writes] AS [WaitingCommandWrites]
		,b.[logical_reads] AS [WaitingCommandLogicalReads]
		,b.[query_plan] AS [WaitingCommandQueryPlan]
		,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM 
	(
		SELECT 
		w.[session_id]
		,s.[original_login_name]
		,s.[login_name]
		,w.[wait_duration_ms]
		,w.[wait_type]
		,r.[STATUS]
		,r.[wait_resource]
		,w.[resource_description]
		,s.[program_name]
		,w.[blocking_session_id]
		,s.[host_name]
		,r.[command]
		,r.[percent_complete]
		,r.[cpu_time]
		,r.[total_elapsed_time]
		,r.[reads]
		,r.[writes]
		,r.[logical_reads]
		,r.[ROW_COUNT]
		,q.[text]
		,q.[dbid]
		,p.[query_plan]
		,r.[plan_handle]
		FROM [sys].[dm_os_waiting_tasks] w
		INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
		INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
		CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
		CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
		WHERE w.[session_id] > 50
		--AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
		--	,'ASYNC_NETWORK_IO')
	) b 
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT';
;
PRINT 'Executed at '+CONVERT(VARCHAR(60), GetDate()) + ' ... waiting '
;
WAITFOR DELAY '0:03:00'   --- wait for 3 minutes
;
GO 120 -- Run repeatedly 120 times (= for 6 hours)

You can run this repeatedly even as #2 is running to see the results.

SELECT * FROM [dbo].[Temp_BlockingItems]
ORDER BY DTRun DESC

• Entered : //2009
• Version :