Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
Small. No user data is modified by this query.
Step 1 - Create Temp Table
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 )
Step 2 - Create Temp Table
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)
Step 3 - View the Results
You can run this repeatedly even as #2 is running to see the results.
SELECT * FROM [dbo].[Temp_BlockingItems] ORDER BY DTRun DESC
Version Information
- Entered : //2009
- Version :
You could leave a comment if you were logged in.