-
Notifications
You must be signed in to change notification settings - Fork 50
/
Locks summary.sql
59 lines (56 loc) · 1.76 KB
/
Locks summary.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Locks summary
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script lists summary information on the per-SPID locks currently held on this database
SELECT dtl.request_session_id,
des.login_name,
dtl.resource_type,
(CASE
WHEN dtl.resource_type = 'OBJECT' THEN
OBJECT_NAME(dtl.resource_associated_entity_id)
WHEN dtl.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' ) THEN
'N/A'
WHEN dtl.resource_type IN ( 'KEY', 'PAGE', 'RID' ) THEN
(
SELECT OBJECT_NAME(object_id)
FROM sys.partitions
WHERE hobt_id = dtl.resource_associated_entity_id
)
ELSE
'Undefined'
END
) AS requested_object_name,
dtl.request_mode AS lock_type,
dtl.request_status,
dtl.request_owner_id AS transaction_id,
DB_NAME(des.database_id) AS database_name
INTO #Locks
FROM sys.dm_tran_locks AS dtl
LEFT OUTER JOIN sys.dm_exec_sessions AS des
ON des.session_id = dtl.request_session_id;
SELECT request_session_id,
login_name,
resource_type,
requested_object_name,
lock_type,
request_status,
transaction_id,
database_name,
COUNT(*) AS [Lock Count]
FROM #Locks
GROUP BY request_session_id,
login_name,
resource_type,
requested_object_name,
lock_type,
request_status,
transaction_id,
database_name
ORDER BY request_session_id,
login_name,
resource_type,
requested_object_name,
lock_type,
request_status,
transaction_id,
database_name;
DROP TABLE #Locks;