Post reply

MSSQL - Job is completing after 24hrs + execution time.

  • April 30, 2009 at 4:20 am

    #130517

    Hi,

    I have a stored procedure which runs the DBReindex command on several tables.

    The tables are grouped into batches, and after each is executed there is a print getdate() command to track the time taken for each section of the query to complete.

    Recently, the SQL job which schedules and runs the procedure at a maintenance window has been taking 24 hours+, although the job completes returning success.

    The job outputs to a log file, and within this I can see that the execution time of the last command (print getdate() ) has only taken around 2 hours.

    In the job history, the run duration is exactly the 2hours, x mins + 24hours.

    I've been trying to find out whats causing the delay and controlling the timeout, but there is no indication in the logs or within the output.

    Any ideas what I'm missing?

    Thanks

    Craig

  • April 30, 2009 at 5:40 am

    #986578

    Are reorganizing indexes or rebuilding them ?

    Also can you post some part of script to make things clearer.

    -Puzzled 🙂

    Cheer Satish 🙂

  • April 30, 2009 at 6:17 am

    #986603

    The stored procedure looks like this:

    PRINT convert(varchar(8), getdate(), 114) + ' - Start'

    DBCC DBREINDEX (table1)

    DBCC DBREINDEX (table2)

    DBCC DBREINDEX (table3)

    PRINT convert(varchar(8), getdate(), 114) + ' - Tables 1-3 Completed'

    DBCC DBREINDEX (table4)

    DBCC DBREINDEX (table5)

    DBCC DBREINDEX (table6)

    PRINT convert(varchar(8), getdate(), 114) + ' - All Tables Completed'

    The time logged with the 'Completed' print statement is around 2 hours after the time in the 'Start' print statement. But the run duration is 26hours.

    Hope that makes more sense

    Thanks

    Craig

  • May 8, 2009 at 1:59 am

    #990907

    Can also verify the actual system tables in MSDB if they show run duration greater than 26 hours

    use msdb

    /* Below script will give job name and description if any which are running more than 20hours , format used

    is HHMMSS */

    select b.name,B.description from sysjobhistory a,sysjobs b

    where run_duration > 20 and a.job_id =b.job_id

    - Sat

    Cheer Satish 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply