????-->SQL SERVER 2000???????????
????SELECT DISTINCT
????'????ID' = STR(a.spid?? 4)
?????? '????ID??' = CONVERT(CHAR(10)?? a.status)
?????? '????????ID' = STR(a.blocked?? 2)
?????? '?????????' = CONVERT(CHAR(10)?? a.hostname)
?????? '???????????' = CONVERT(CHAR(10)?? SUSER_NAME(a.uid))
?????? '???????' = CONVERT(CHAR(10)?? DB_NAME(a.dbid))
?????? '??ó?????' = CONVERT(CHAR(10)?? a.program_name)
?????? '??????е?????' = CONVERT(CHAR(16)?? a.cmd)
?????? '?????' = a.loginame
?????? '??????' = b.text
FROM master..sysprocesses a CROSS APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.blocked IN ( SELECT blocked
FROM master..sysprocesses )
-- and blocked <> 0
ORDER BY STR(spid?? 4)
--Result
/*
????ID  ????ID   ??  ????????ID  ????????? ??????????? ??????? ??ó????? ??????е????? ????? ??????
---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- -------------------------
56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF
57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF
58 suspended 57 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442/Administrator begin tran select * from ta
59 runnable 0 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442/Administrator SELECT DISTINCT
60 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442/Administrator SET FMTONLY OFF;
62 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442/Administrator
*/
????--??????????(spid:57??58)
????select connect_time??last_read??last_write??most_recent_sql_handle
????from sys.dm_exec_connections where session_id in(57??58)
????--???????
????select login_time??host_name??program_name??login_name??last_request_start_time??last_request_end_time
????from sys.dm_exec_sessions where session_id in(57??58)
????--????????????е?????
select
session_id??blocking_session_id??wait_type??wait_time??wait_resource
from
sys.dm_exec_requests
where
blocking_session_id>0--???????????????? ID??????????? NULL??????????????
/*
session_id??blocking_session_id??wait_type??wait_time??wait_resource
58 57 LCK_M_S 2116437 KEY: 6:72057594039435264 (020068e8b274)
*/
????--????????е?SQL???
select
a.session_id??sql.text??a.most_recent_sql_handle
from
sys.dm_exec_connections a
cross apply
sys.dm_exec_sql_text(a.most_recent_sql_handle) as SQL --????ú???fn_get_sql???most_recent_sql_handle?????????
where
a.Session_id in(57??58)
/*
session_id text
----------- -----------------------------------------------
57 SET STATISTICS XML OFF
58 begin tran select * from ta
*/