[SQL Server] Afficher les objets bloqués
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id)AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction AS IsUserTransaction,
AT.name AS TransactionName,
CN.auth_scheme AS AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id= P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSSAPPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle)AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
EXEC SP_LOCK
EXEC SP_LOCK 10
EXEC SP_WHO2
EXEC SP_WHO2 active
-- Afficher la requête lancée par la session
DBCC INPUTBUFFER(10)
--Trouver le niveau d'imbrication des transactions et l'état du processus de la SPID bloquant
SELECT open_tran FROMmaster.sys.sysprocessesWHERE SPID=10
-- Monitor current transactions and get all required information
select
S.[host_name],
DB_NAME(R.database_id) as [database_name],
(CASE WHEN S.program_name like 'SQLAgent - TSQL JobStep (Job %' THEN j.name ELSE S.program_name END) as Name ,
S.login_name, R.status, R.command
,b.text, R.blocking_session_id, R.percent_complete, R.session_id
, R.wait_type,R.wait_time,
isnull(DATEDIFF(mi, S.last_request_start_time, getdate()), 0) [MinutesRunning]
,qp.query_plan
FROM sys.dm_exec_requests R with (nolock)
INNER JOIN sys.dm_exec_sessions S with (nolock)
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) b
OUTER APPLY sys.dm_exec_query_plan(R.plan_handle) AS qp
LEFT OUTER JOIN msdb.dbo.sysjobs J with (nolock)
ON (substring(left(j.job_id,8),7,2) +
substring(left(j.job_id,8),5,2) +
substring(left(j.job_id,8),3,2) +
substring(left(j.job_id,8),1,2)) = substring(S.program_name,32,8)
WHERE R.session_id > 50
and R.session_id <> @@SPID
and S.[host_name] IS NOT NULL
ORDER BY s.[host_name],S.login_name;
--------------
Select t1.spid ,t1.status, loginame=rtrim(t1.loginame), hostname=LEFT(rtrim(t1.hostname),20), program_name=rtrim(t1.program_name), t1.blocked,t1.dbid,
dbname = rtrim((case when t1.dbid = 0 then null when t1.dbid <> 0 then db_name(t1.dbid) end)),rtrim(t1.nt_username)nt_username,
rtrim(t1.cmd)cmd,
datediff(minute,t1.last_batch,GETDATE()) waittime_dk,
substring(sql.text, stmt_start/2,CASE WHEN stmt_end<1 THEN 8000 ELSE (stmt_end-stmt_start)/2 END) AS RunningSqlText,
sql.text as FullSqlText,
t1.cpu, substring( convert(varchar,t1.last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,t1.last_batch,113) ,13 ,8 )
as 'last_batch_time',
t1.waittime waittime,
t1.lastwaittype
From master.dbo.sysprocesses(NOLOCK) t1
cross apply sys.dm_exec_sql_text(t1.sql_handle) AS sql
Where t1.blocked <> 0 OR t1.spid in(Select t2.blocked From master.dbo.sysprocesses(NOLOCK) t2)
Order By t1.dbid DESC,t1.spid