With the help I got from an earlier question, I've created a stored
procedure that will kill a process that has been blocking other processes
for x amount of time. If a process is killed, the SP generates an email
using xp_smtp_sendmail that states the process that was killed and when. I
would also like the email to include the output of dbcc inputbuffer(spid)
so I can keep track of what processes have a tendancy to block for a longer
period of time.
I haven't been able to figure out how to get the output of dbcc inputbuffer
into the email message. I'd appreciate anyone that might help me figure
this out.
Thanks in advance,
Andrea
On Mon, 25 Jul 2005 13:54:19 -0700, "Juan Vera " <mssqldba-ezmlmshield-
x18735914.[Email address protected] wrote :
> Andrea;
>
> This is what I wrote to handle the problem - you are free to use it. Read
through the variable declarations for an idea of what portions you can
control.
>
> http://www.powerslacker.com/Juan/CodeSamples/jvBlockMonitor.sql
>
> jvBlockMonitor can be installed in master and run on a one-minute
schedule, and has saved my butt several times. In the SQL agent schedule,
you can control the values for how aggressively it behaves.
>
> The most important thing is that you can define a service account,
because in most cases there are certain operations performed as batches
that you want to be much less aggressive about killing.
>
> The procedure relies on xp_smtp_sendmail for notifications, but this part
is not indispensable.
>
> Enjoy
>
> Juan Cristián
>
> -----Original Message-----
> From: Andrea Miller [mailto:mssqldba-ezmlmshield-x94042273.[Email address
protected]
> Sent: Monday, July 25, 2005 12:20 PM
> To: LazyDBA Discussion
> Subject: kill blocking processes
>
> Does anyone know of a way to monitor processes that are blocking other
> processes and kill them if they block for a certain period of time?
(This
> is with SQL 2000.) For instance, I'd like to kill any process that
blocks
> out other processes for a time of ten minutes or so.
>
> Thanks in advance,
>
> Andrea
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
MS Sql Server LazyDBA home page