SQL Server Performance Forum – Threads Archive

Open Transactions – quick help.

Hi, I have these two scripts that essentially do the same: 1)
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2) 2)
select count (*), max(waittime/1000) from sysprocesses where status <> ‘background’ and cmd not in (‘signal handler’, ‘lock monitor’, ‘log writer’, ‘lazy writer’, ‘checkpoint sleep’, ‘awaiting command’) and open_tran <>0 having max(waittime/1000)>30 Please help me on which would be more accurate for getting open trans? Do I need the join for syslocks? W/O it, it seems to get the same data? The first one joins on syslocks and uses datediff on last_batch, where the second just get it where max waitime is greater than 30. What are the differences! THANKS!

---

Is it for SQL2000 or 2005? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

Well, originated in 2000, but works on 2005 (backward compatibility). I should have posted maybe in 2000. Answers?

---

I’m trying to get blocks and active open trans to return a count for each (should be one row returned by doesn’t_ select
case when blocked > 0 then count(blocked) else 0 end as blocked,
case when open_tran > 0 then count(open_tran) else 0 end as open_trans–, max(waittime/1000)
from master..sysprocesses p
where status <> ‘background’ and cmd not in (‘signal handler’, ‘lock monitor’, ‘log writer’, ‘lazy writer’, ‘checkpoint sleep’, ‘awaiting command’)
and (open_tran >0
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)) or blocked <>0
group by blocked, open_tran
having max(waittime/1000)>5

---

]]>