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 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| select (dense_rank() over(order by (case when at.transaction_type = 4 then case at.dtc_state when 1 then ''Active'' when 2 then ''Prepared'' when 3 then ''Committed'' when 4 then ''Aborted'' when 5 then ''Recovered'' end else case at.transaction_state when 0 then ''Invalid'' when 1 then ''Initialized'' when 2 then ''Active'' when 3 then ''Ended'' when 4 then ''Commit Started'' when 5 then ''Prepared'' when 6 then ''Committed'' when 7 then ''Rolling Back'' when 8 then ''Rolled Back'' end end)))%2 as l1
, (dense_rank() over(order by (case when at.transaction_type = 4 then case at.dtc_state when 1 then ''Active'' when 2 then ''Prepared'' when 3 then ''Committed'' when 4 then ''Aborted'' when 5 then ''Recovered'' end else case at.transaction_state when 0 then ''Invalid'' when 1 then ''Initialized'' when 2 then ''Active'' when 3 then ''Ended'' when 4 then ''Commit Started'' when 5 then ''Prepared'' when 6 then ''Committed'' when 7 then ''Rolling Back'' when 8 then ''Rolled Back'' end end),st.session_id, s.host_name, s.program_name, s.login_name, s.login_time))%2 as l2
, (dense_rank() over(order by (case when at.transaction_type = 4 then case at.dtc_state when 1 then ''Active'' when 2 then ''Prepared'' when 3 then ''Committed'' when 4 then ''Aborted'' when 5 then ''Recovered'' end else case at.transaction_state when 0 then ''Invalid'' when 1 then ''Initialized'' when 2 then ''Active'' when 3 then ''Ended'' when 4 then ''Commit Started'' when 5 then ''Prepared'' when 6 then ''Committed'' when 7 then ''Rolling Back'' when 8 then ''Rolled Back'' end end),st.session_id, s.host_name, s.program_name, s.login_name, s.login_time, dt.transaction_id))%2 as l3
, (dense_rank() over(order by (case when at.transaction_type = 4 then case at.dtc_state when 1 then ''Active'' when 2 then ''Prepared'' when 3 then ''Committed'' when 4 then ''Aborted'' when 5 then ''Recovered'' end else case at.transaction_state when 0 then ''Invalid'' when 1 then ''Initialized'' when 2 then ''Active'' when 3 then ''Ended'' when 4 then ''Commit Started'' when 5 then ''Prepared'' when 6 then ''Committed'' when 7 then ''Rolling Back'' when 8 then ''Rolled Back'' end end),st.session_id, s.host_name, s.program_name, s.login_name, s.login_time, dt.transaction_id ,(case when obj.name is null then ''Other resources'' else obj.name end)))%2 as l4
, (dense_rank() over(order by (case when at.transaction_type = 4 then case at.dtc_state when 1 then ''Active'' when 2 then ''Prepared'' when 3 then ''Committed'' when 4 then ''Aborted'' when 5 then ''Recovered'' end else case at.transaction_state when 0 then ''Invalid'' when 1 then ''Initialized'' when 2 then ''Active'' when 3 then ''Ended'' when 4 then ''Commit Started'' when 5 then ''Prepared'' when 6 then ''Committed'' when 7 then ''Rolling Back'' when 8 then ''Rolled Back'' end end),st.session_id, s.host_name, s.program_name, s.login_name, s.login_time, dt.transaction_id,(case when obj.name is null then ''Other resources'' else obj.name end),tl.resource_type))%2 as l5
, dense_rank() over(partition by case when at.transaction_type =4 then case at.dtc_state when 1 then 2 when 2 then 5 when 3 then 6 when 4 then 9 when 5 then 10 else at.dtc_state*2 end else at.transaction_state end order by dt.transaction_id)as rank
, st.session_id
, s.host_name
, s.program_name
, s.login_name
, s.login_time
, s.host_process_id
, dt.transaction_id as tran_id
, at.name
, at.transaction_begin_time as tran_start_time
, case when at.transaction_type = 4
then case at.dtc_state
when 1 then ''Active''
when 2 then ''Prepared''
when 3 then ''Committed''
when 4 then ''Aborted''
when 5 then ''Recovered''
end
else case at.transaction_state
when 0 then ''Invalid''
when 1 then ''Initialized''
when 2 then ''Active''
when 3 then ''Ended''
when 4 then ''Commit Started''
when 5 then ''Prepared''
when 6 then ''Committed''
when 7 then ''Rolling Back''
when 8 then ''Rolled Back''
end
end as state
, case when at.transaction_type = 4
then case at.dtc_isolation_level
when 0xffffffff then ''Unknown''
when 0x10 then ''Chaos''
when 0x100 then ''Read Uncommitted''
when 0x1000 then ''Read Committed''
when 0x10000 then ''Repeatable Read''
when 0x100000 then ''Serializable''
when 0x100000 then ''Isolated''
end
else case Convert(int,r.transaction_isolation_level)
when 1 then ''Read Uncommitted''
when 2 then ''Read Committed''
when 3 then ''Repeatable Read''
when 4 then ''Serializable''
when 5 then ''Snapshot''
else ''Unknown''
end
end as transaction_isolation_level
, at.transaction_type as tran_type
, (select count(*) from sys.dm_tran_locks tlock where (tlock.request_owner_id = dt.transaction_id and tlock.resource_database_id = db_id()) ) as ''total''
, tl.resource_type
, case when obj.name is null then ''Other resources'' else obj.name end as resource_name
, convert( varchar,tl.request_mode) as request_mode
, (select count(*) from sys.dm_tran_locks tlock left outer join sys.partitions pt1 on ( pt1.hobt_id = tlock.resource_associated_entity_id ) left outer join sys.objects obj1 on ( obj1.object_id in (pt1.object_id, tlock.resource_associated_entity_id) ) where ( 1 = (case when obj.object_id is null then 1 else case when obj1.object_id = obj.object_id then 1 else 0 end end ) ) and (tlock.resource_type = tl.resource_type) and (tlock.request_mode = tl.request_mode) and (tlock.request_owner_id = dt.transaction_id) and tlock.resource_database_id = db_id() and (tlock.request_status = ''GRANT'') ) as ''Granted''
, (select count(*) from sys.dm_tran_locks tlock left outer join sys.partitions pt1 on ( pt1.hobt_id = tlock.resource_associated_entity_id ) left outer join sys.objects obj1 on ( obj1.object_id in (pt1.object_id, tlock.resource_associated_entity_id) ) where ( 1 = (case when obj.object_id is null then 1 else case when obj1.object_id = obj.object_id then 1 else 0 end end ) ) and (tlock.resource_type = tl.resource_type) and (tlock.request_mode = tl.request_mode) and (tlock.request_owner_id = dt.transaction_id) and tlock.resource_database_id = db_id() and (tlock.request_status = ''WAIT'') ) as ''Waiting''
, (select count(*) from sys.dm_tran_database_transactions where transaction_id = dt.transaction_id) as db_span_count
, st.is_local
from sys.dm_tran_database_transactions dt
left outer join sys.dm_tran_locks tl on ( (tl.request_owner_id = dt.transaction_id) and ( tl.resource_database_id = DB_ID() ) )
inner join sys.dm_tran_active_transactions at on (at.transaction_id = dt.transaction_id)
inner join sys.dm_tran_session_transactions st on (st.transaction_id = dt.transaction_id)
left outer join sys.dm_exec_sessions s on ( st.session_id = s.session_id )
left outer join sys.dm_exec_requests r on (r.transaction_id = dt.transaction_id)
left outer join sys.partitions pt on ( pt.hobt_id = case when tl.resource_type in (''RID'', ''KEY'', ''PAGE'', ''EXTENT'') then (tl.resource_associated_entity_id) else null end )
left outer join sys.objects obj on ( obj.object_id = ( case when tl.resource_type in (''TABLE'') then tl.resource_associated_entity_id when tl.resource_type in (''RID'', ''KEY'', ''PAGE'', ''EXTENT'') then pt.object_id else null end ) )
where (dt.database_id = DB_ID()) and (st.is_user_transaction=1)
group by st.session_id, s.host_name, s.program_name, s.login_name, s.login_time, s.host_process_id, dt.transaction_id,at.name, at.transaction_begin_time, at.transaction_state, at.dtc_state, r.transaction_isolation_level, at.dtc_isolation_level, at.transaction_type, obj.object_id, obj.name, tl.resource_type, tl.request_mode, st.is_local order by st.session_id, dt.transaction_id,at.name, r.transaction_isolation_level, at.transaction_type, obj.object_id, obj.name, tl.resource_type, tl.request_mode |
Partager