Post reply

Backup Job Lengths?

  • February 17, 2009 at 9:14 am

    #130983

    Hello everyone,

    I having a hard time locating information on a backup job durations. Need to determine the time it takes to execute a full backup of all our SQL servers (2000 and 2005). Can get the start times but not the time it took to perform the job.

    Could look at the backup files but I have too many servers do this on and I would like to automate it.

    Hoping that someone would have some code/script that would produce this information.

    Thanks for you help in advance,

    Rudy

    Rudy

  • February 17, 2009 at 12:03 pm

    #945363

    Right click on the job view history your see duration

  • February 17, 2009 at 12:20 pm

    #945371

    Hello TRACEY,

    Thanks for the info, but I'm looking some sort of script to execute. I don't want to do a manual process on over 80 servers.

    Thanks,

    Rudy

    Rudy

  • February 17, 2009 at 12:23 pm

    #945378

    If you have these set up as SQL Agent tasks, you could create a script using msdb..sysjobs_view and msdb..sp_help_jobhistory, that way after creating a list of your jobs, you could have your script connect to each of your servers and report back the last execution time and such, perhaps dump it to a table from which you could query it each day or have it sent as an email via SSRS or some other method.

    Or if you do a bit of digging on this site or google, you might come up with a ready made script that does all this for you.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • February 17, 2009 at 12:52 pm

    #945399

    Hello Luke,

    Thanks for the info but I cannot seem to get the duration of the job. I can get when the backups were started but not the time it took to backup each database.

    I've looking around but either it cannot be done or no one has look at this before.

    Thanks,

    Rudy

    Rudy

  • February 17, 2009 at 12:55 pm

    #945402

    The run_duration column of sp_help_jobhistory doesn't give you the run duration?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • February 17, 2009 at 1:03 pm

    #945414

    sp_help_jobhistory seems to give you run_time but is this the duration or time started? I think it's in milliseconds.. I have to look more at this.

    Thanks,

    Rudy

    Rudy

  • February 17, 2009 at 1:17 pm

    #945429

    the run_date column shows the date the step ran.

    the run_time column shows the time the step ran, ie 140000 = 14:00:00 = 2:00 PM

    the run_duration column gives you the amount of time in minutes and seconds that the job took. 215 = 2:15 = 2 min 15 seconds.

    The sp_help_jobhistory stored procedure is the procedure that Enterprise manager calls when opening and displaying data in the view job history dialog box. (You can verify this by running profiler and viewing the job history.) If you can see it in the app it comes from that procedure. So the run_date and run_time columns are combined to show the Run At column in the dialog box. The run_duration column is used to populate the Run Duration column of the history dialog box.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • February 17, 2009 at 1:21 pm

    #945436

    Excellent! Thanks Luke! This is what I'm looking for. Now I'll need to see if I can create a proc around this so that I can specify a job or output to a table.

    Many thanks,

    Rudy

    Rudy

Viewing 9 posts - 1 through 8 (of 8 total)

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