select blocking sessions t-sql

Is there a table or structure that contains information that I can see
via the Activity Monitor? I've searched the msdb database and I've not
found any tables that have what I am looking for.
I have tools in Cognos BI that can monitor tables, see changes and
then send email. I want to monitor for blocked processes then react to
them.
We have SQL Server 2005.
TIA

re: select blocking sessions t-sql

rcamarda (robert.a.camarda@gmail.com) writes:You can detect blocking through sys.dm_os_waiting_tasks. For instance
you could poll it once a minute or so, and if wait_duration_ms exceeds
a certain threshold *and* session_id is >= 51 *and* there is a
blocking_session_id <session_id, you could send an alert or whatever.
You probably have to test to see that you don't get too many false alarms.

You may also be interested at looking at my beta_lockinfo,
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: select blocking sessions t-sql

>Thanks Erland, it is perfect for 2005!
Now, I need same thing for SQL Server 2000. As far as I can tell there
sys.dm_os_waiting_tasks doesnt exist in 2000.
Can you help one more time?
(BTW, what key words could I use to search? JOBS and TABLE mostly got
hits on emploment opportunities)
TIA

re: select blocking sessions t-sql

rcamarda (robert.a.camarda@gmail.com) writes:The best bet in SQL 2000 is the column master.dbo.sysprocesses.blocked.

My old aba_lockinfo runs on SQL 2000:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Search for what? I'm pretty bad at searching myself. But if you mean
SQL Server jobs, I guess it helps putting in "Agent". And "SQL Server"
of course.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: select blocking sessions t-sql

rcamarda (robert.a.camarda@gmail.com) writes:Also, this blog post from SQL Server MVP Tony Rogerson can be useful:
http://sqlblogcasts.com/blogs/tonyro...e-it-work.aspx

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx