"sarah" wrote:

> Hi,
>
> I have a job running every day which does insert and update one of the huge
> table. It will be fine (takes about 4 minutes each time to complete) for a
> couple of months and then all of sudden it runs for hours and blocking the
> whole table and brings down the application. Before I can figure out the
> issue, is there anyway for me to detect this , cancel the job and send me the
> notification?
>
> thanks a lot,
> Sarah

"Linchi Shea" wrote:

> To alert you on sustained blocking, you can grab one of blocking detection
> scripts from the MS site. Do a search on SQL Server blokcing scripts, you'll
> find many variations of the script.
>
> Alternatively, you can just expand on the following simple query:
>
> select * from master..sysprocesses where spid in (select blocked from
> master..sysprocesses) or block != 0
>
> and check the waititme column. If it crosses a htreshold, fire off an alert
> (e.g. send an email).
>
> To kill your specific long running script, you can log its spid to a table,
> and modify the above simple query to periodically check for that specific
> spid plus some additional info such as hostname, program name, etc to ensure
> that you are dealing with the bad script instead of something else that
> happens to be grabbing the spid. If the spid is blocking others for a
> sustained time period, kill it.
>
> Linchi
>
> "sarah" wrote:
>
> > Hi,
> >
> > I have a job running every day which does insert and update one of the huge
> > table. It will be fine (takes about 4 minutes each time to complete) for a
> > couple of months and then all of sudden it runs for hours and blocking the
> > whole table and brings down the application. Before I can figure out the
> > issue, is there anyway for me to detect this , cancel the job and send me the
> > notification?
> >
> > thanks a lot,
> > Sarah

"sarah" <sa***@discussions.microsoft.com> wrote in message
news:AC8BF526-462E-454F-923D-21A95012049C@microsoft.com...
> Hi,
>
> I have a job running every day which does insert and update one of the
> huge
> table. It will be fine (takes about 4 minutes each time to complete) for a
> couple of months and then all of sudden it runs for hours and blocking the
> whole table and brings down the application. Before I can figure out the
> issue, is there anyway for me to detect this , cancel the job and send me
> the
> notification?
>
> thanks a lot,
> Sarah

"Robert Lakinski" wrote:

> If the job's session is the blocked session ( blocked, not blocking  ) you
> can set the lock timeout parameter to a resonable value (ex.30 sec)
> at the begginig of your T-Sql code.
>
> SET LOCK_TIMEOUT  30000
>
> If your session is waiting for a resource, after 30 sec the job step will
> fail with the message: Lock request time out period exceeded
>
>
> Robert Lakinski
>
>
> "sarah" <sa***@discussions.microsoft.com> wrote in message
> news:AC8BF526-462E-454F-923D-21A95012049C@microsoft.com...
> > Hi,
> >
> > I have a job running every day which does insert and update one of the
> > huge
> > table. It will be fine (takes about 4 minutes each time to complete) for a
> > couple of months and then all of sudden it runs for hours and blocking the
> > whole table and brings down the application. Before I can figure out the
> > issue, is there anyway for me to detect this , cancel the job and send me
> > the
> > notification?
> >
> > thanks a lot,
> > Sarah
>
>

"sarah" <sa***@discussions.microsoft.com> wrote in message
news:0AF4CAC0-199E-4995-A633-243B7F2737F9@microsoft.com...
> Hi Robert,
>
> My job is calling one stored procedure. Can i put this
> SET LOCK_TIMEOUT  30000 at the beginning of the stored procedure?
> Where does the error message send, screen or email?
> In another word, how do I catch this error message?
>
> Thanks a lot,
> Sarah
>
> "Robert Lakinski" wrote:
>
>> If the job's session is the blocked session ( blocked, not blocking  )
>> you
>> can set the lock timeout parameter to a resonable value (ex.30 sec)
>> at the begginig of your T-Sql code.
>>
>> SET LOCK_TIMEOUT  30000
>>
>> If your session is waiting for a resource, after 30 sec the job step will
>> fail with the message: Lock request time out period exceeded
>>
>>
>> Robert Lakinski
>>
>>
>> "sarah" <sa***@discussions.microsoft.com> wrote in message
>> news:AC8BF526-462E-454F-923D-21A95012049C@microsoft.com...
>> > Hi,
>> >
>> > I have a job running every day which does insert and update one of the
>> > huge
>> > table. It will be fine (takes about 4 minutes each time to complete)
>> > for a
>> > couple of months and then all of sudden it runs for hours and blocking
>> > the
>> > whole table and brings down the application. Before I can figure out
>> > the
>> > issue, is there anyway for me to detect this , cancel the job and send
>> > me
>> > the
>> > notification?
>> >
>> > thanks a lot,
>> > Sarah
>>
>>

"Robert Lakinski" wrote:

> Hi Sarah,
>
>  You can put the the 'set lock_timeout...' statement as a first line of the
> job step's t-sql code:
>
> ---------
> SET LOCK_TIMEOUT  30000;
> Exec YourStoredProc;
> ----------
>
>
> Robert Lakinski
>
>
> "sarah" <sa***@discussions.microsoft.com> wrote in message
> news:0AF4CAC0-199E-4995-A633-243B7F2737F9@microsoft.com...
> > Hi Robert,
> >
> > My job is calling one stored procedure. Can i put this
> > SET LOCK_TIMEOUT  30000 at the beginning of the stored procedure?
> > Where does the error message send, screen or email?
> > In another word, how do I catch this error message?
> >
> > Thanks a lot,
> > Sarah
> >
> > "Robert Lakinski" wrote:
> >
> >> If the job's session is the blocked session ( blocked, not blocking  )
> >> you
> >> can set the lock timeout parameter to a resonable value (ex.30 sec)
> >> at the begginig of your T-Sql code.
> >>
> >> SET LOCK_TIMEOUT  30000
> >>
> >> If your session is waiting for a resource, after 30 sec the job step will
> >> fail with the message: Lock request time out period exceeded
> >>
> >>
> >> Robert Lakinski
> >>
> >>
> >> "sarah" <sa***@discussions.microsoft.com> wrote in message
> >> news:AC8BF526-462E-454F-923D-21A95012049C@microsoft.com...
> >> > Hi,
> >> >
> >> > I have a job running every day which does insert and update one of the
> >> > huge
> >> > table. It will be fine (takes about 4 minutes each time to complete)
> >> > for a
> >> > couple of months and then all of sudden it runs for hours and blocking
> >> > the
> >> > whole table and brings down the application. Before I can figure out
> >> > the
> >> > issue, is there anyway for me to detect this , cancel the job and send
> >> > me
> >> > the
> >> > notification?
> >> >
> >> > thanks a lot,
> >> > Sarah
> >>
> >>
>
>