Connecting Tech Pros Worldwide Help | Site Map

select blocking sessions t-sql

  #1  
Old August 7th, 2008, 09:15 PM
rcamarda
Guest
 
Posts: n/a
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
  #2  
Old August 7th, 2008, 10:45 PM
Erland Sommarskog
Guest
 
Posts: n/a

re: select blocking sessions t-sql


rcamarda (robert.a.camarda@gmail.com) writes:
Quote:
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.
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

  #3  
Old August 8th, 2008, 01:55 PM
rcamarda
Guest
 
Posts: n/a

re: select blocking sessions t-sql


>
Quote:
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, esq...@sommarskog.se
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
  #4  
Old August 8th, 2008, 10:45 PM
Erland Sommarskog
Guest
 
Posts: n/a

re: select blocking sessions t-sql


rcamarda (robert.a.camarda@gmail.com) writes:
Quote:
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?
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.
Quote:
(BTW, what key words could I use to search? JOBS and TABLE mostly got
hits on emploment opportunities)
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

  #5  
Old August 10th, 2008, 08:05 PM
Erland Sommarskog
Guest
 
Posts: n/a

re: select blocking sessions t-sql


rcamarda (robert.a.camarda@gmail.com) writes:
Quote:
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.
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

Closed Thread


Similar Threads
Thread Thread Starter Forum Replies Last Post
urgent : program for server which handles multiple clients kurrachaitanya answers 1 October 30th, 2008 12:01 AM
Performance bottleneck in bulk updates/inserts shreyask answers 4 September 25th, 2008 03:04 PM
Page load frequency frizzle answers 11 December 21st, 2006 10:35 AM
AutoKeys Macro ApexData@gmail.com answers 4 March 24th, 2006 05:35 PM
Windows service using the socket module JDF answers 1 November 6th, 2005 09:05 PM