Tuesday, 2 February 2010

Open Transactions with SQL text

Just trying to track down which spid is bashing my poor little SQL machine.
Quite coincidentally Paul Randall just blogged about a new script to show who has open transactions on the server.

Only problem is I want the same information in SQL 2000 land ... :(

Best I can come up with at the moment is,


   1:  select [spid], [blocked], [waittime], [lastwaittype]
   2:    , [waitresource], [dbid], [cpu], [physical_io]
   3:    , [memusage], [login_time], [last_batch], [open_tran]
   4:    , [status], [hostname], [program_name]
   5:  from master..sysprocesses
   6:  where [spid] > 51
   7:    and [blocked] > 0
   8:  order by [blocked], spid
   9:   
  10:  DECLARE @Handle binary(20)
  11:  SELECT @Handle = sql_handle FROM master..sysprocesses 
  12:  WHERE spid = 527  
  13:  SELECT * FROM ::fn_get_sql(@Handle)

No comments: