blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisR
Hi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>
|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>