Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    19

    Unanswered: SQL Server Maintenance Jobs

    Is there a way to set up an alert that will tell me when a scheduled maintenance job (Like transaction log backup) is running too long or that it has not completed. I had a transaction log backup run for over 15 hours one night, thus all my other jobs would not run. Any suggestions would be great.

  2. #2
    Join Date
    Jul 2002
    Posts
    63
    Hi I have the same problem . Do you know find any way to stop the job if it running so long ?

  3. #3
    Join Date
    Jul 2002
    Posts
    63
    Originally posted by eschapir
    Hi I have the same problem . Do you know find any way to stop the job if it running so long ?
    I found the solution you can create second job that schedule 15 min after the first one . Check if the first job is running and if yes use Sp_kill in order to stop the running job

  4. #4
    Join Date
    Jun 2002
    Posts
    19
    Originally posted by eschapir
    Hi I have the same problem . Do you know find any way to stop the job if it running so long ?
    I have not found a way for the job to stop, but I did write a scripi to e-mail and page me if it happens. Here is the script.

    CREATE proc sp_check_job_running
    @job_name char(50),
    @minutes_allowed int,
    @person_to_notify varchar(50)
    AS

    DECLARE @var1 char(1),
    @process_id char(8),
    @job_id_char char(8),
    @minutes_running int,
    @message_text varchar(255)

    select @job_id_char = substring(CAST(job_id AS char(50)),1,8)
    from msdb..sysjobs
    where name = @job_name

    select @process_id = substring(@job_id_char,7,2) +
    substring(@job_id_char,5,2) +
    substring(@job_id_char,3,2) +
    substring(@job_id_char,1,2)


    select @minutes_running = DATEDIFF(minute,last_batch, getdate())
    from master..sysprocesses
    where program_name LIKE ('%0x' + @process_id +'%')

    if @minutes_running > @minutes_allowed
    BEGIN
    select @message_text = ('Job '
    + UPPER(SUBSTRING(@job_name,1,LEN(@job_name)))
    + ' has been running for '
    + SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5))))
    + ' minutes, which is over the allowed run time of '
    + SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5))))
    + ' minutes.')
    EXEC master..xp_sendmail
    @recipients = @person_to_notify,
    @message = @message_text,
    @subject = 'Long-Running Job to Check'
    END

    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •