Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Locks details

Get current locks details:

SELECT  EC2.session_id AS 'Blocker Process Id',
        ES2.login_name AS 'Blocker Login Name',
        ES2.program_name AS 'Blocker Program',
        DB_Name(ER2.database_id) AS 'Blocked DB Name',
        ES2.host_name AS 'Blocker Computer',
        CA2.[text] AS 'Blocker Command',
        EC1.session_id AS 'Blocked Process Id',
        ES1.login_name AS 'Blocked Login Name',
        ES1.program_name AS 'Blocked Program',
        DB_Name(ER1.database_id) AS 'Blocker DB Name',
        ES1.host_name AS 'Blocked Computer',
        CA1.[text] AS 'Blocked Command',
        Cast(Cast((WT1.wait_duration_ms/60000.0) As Decimal(15,2)) As VarChar) + ' min.' 
AS 'Blocked Time',
        (Case When WT2.session_id Is Null Then 'Top level blocker' Else 'Secondary blocker' End)
AS 'Block Level'
FROM sys.dm_exec_connections EC1
INNER JOIN sys.dm_exec_sessions ES1 ON EC1.session_id=ES1.session_id
INNER JOIN sys.dm_exec_requests ER1 ON EC1.session_id=ER1.session_id
CROSS APPLY sys.dm_exec_sql_text(EC1.most_recent_sql_handle) CA1
INNER JOIN sys.dm_os_waiting_tasks WT1 ON EC1.session_id=WT1.session_id
LEFT JOIN sys.dm_os_waiting_tasks WT2 ON WT1.blocking_session_id=WT2.session_id
INNER JOIN sys.dm_exec_connections EC2 ON WT1.blocking_session_id=EC2.session_id
INNER JOIN sys.dm_exec_sessions ES2 ON EC2.session_id=ES2.session_id
INNER JOIN sys.dm_exec_requests ER2 ON EC2.session_id=ER2.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(EC2.most_recent_sql_handle) CA2
ORDER BY 'Blocked Time' Desc

No comments:

Post a Comment