|
avipenina
Starting Member
44 Posts |
 Posted - 2014-09-05 : 17:20:32
|
| Hii've this script that find a block on a table with the syntax.how can update the script that will show me the lock and the syntax that causing the lock and other info in the same results?THXWITH [Blocking]AS (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'))SELECT 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 [Blocking] bINNER JOIN [sys].[dm_exec_sessions] s1ON b.[blocking_session_id] = s1.[session_id]INNER JOIN [sys].[dm_tran_locks] tON t.[request_session_id] = b.[session_id]WHERE t.[request_status] = 'WAIT'GO |
|
find locked syntax