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 :
 +
 +
 +