select blocking sessions t-sql

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rcamarda
    #1

    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
    Tags: None
    • Erland Sommarskog
      #2
      Re: select blocking sessions t-sql

      rcamarda (robert.a.camar da@gmail.com) writes:
      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_waiti ng_tasks. For instance
      you could poll it once a minute or so, and if wait_duration_m s exceeds
      a certain threshold *and* session_id is >= 51 *and* there is a
      blocking_sessio n_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@sommarsk og.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

        Comment

        • rcamarda
          #3
          Re: select blocking sessions t-sql

          >
          You can detect blocking through sys.dm_os_waiti ng_tasks. For instance
          you could poll it once a minute or so, and if wait_duration_m s exceeds
          a certain threshold *and* session_id is >= 51 *and* there is a
          blocking_sessio n_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...@sommarsk og.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_waiti ng_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

            Comment

            • Erland Sommarskog
              #4
              Re: select blocking sessions t-sql

              rcamarda (robert.a.camar da@gmail.com) writes:
              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_waiti ng_tasks doesnt exist in 2000.
              Can you help one more time?
              The best bet in SQL 2000 is the column master.dbo.sysp rocesses.blocke d.

              My old aba_lockinfo runs on SQL 2000:
              http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
              (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@sommarsk og.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

                Comment

                • Erland Sommarskog
                  #5
                  Re: select blocking sessions t-sql

                  rcamarda (robert.a.camar da@gmail.com) writes:
                  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:
                  Database Error
                  http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx


                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

                    Comment

                    Previous template Next
                    Working...