Differences

This shows you the differences between two versions of the page.

Link to this comparison view

— control_sql_-_log_database_record_locks_every_few_minutes [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== 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.
 +
 +
 +
 +<code sql>
 +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
 +)
 +</code>
 +
 +
 +
 +===== 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.)
 +
 +
 +
 +<code sql>
 +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)
 +</code>
 +
 +
 +
 +===== Step 3 - View the Results =====
 +
 +
 +
 +You can run this repeatedly even as #2 is running to see the results.
 +
 +
 +
 +<code sql>
 +SELECT * FROM [dbo].[Temp_BlockingItems]
 +ORDER BY DTRun Desc
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : __/__/2009
 +  * Version :
 +
 +
 +