0

Need a graphical representation of my Job run-times in SQL Server.

I would like to know if there is a software or solution for getting the jobs in to a calendar looking format or Gantt style. I was hoping to have it in Outlook calendar, but it doesn't have to be.

How do most DBAs chart what jobs/backups are running at certain times? I've looked online and found that such information is not easy to come by.

Microsoft Project is out of the question (too expensive).

New contributor
Edomawadagbon is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • I do it using a script. very simples. if I need to show it to someone, or find out the best time to schedule something, I can export it to excel. would this be enough for you? – Marcello Miorelli May 24 at 10:30
0

I've done this before using ASCII art. I don't have the code to hand but this is the gist of it.

We have a table (or view) of job names with start and end times. The interval of interest is defined by two variables - @IntervalStart and @IntervalEnd.

The output will be an A4 sheet. A Word document using Courier New 10 pt and default margins has a width of 115 characters in landscape (I found portrait too restrictive). Allow, say, 30 characters for a job name and separation; that gives 80 characters to represent the gantt chart. Therefore each character represents DATEDIFF(MINUTE, @IntervalStart, @IntervalEnd) / 80 minutes. Let's call this @Step. Change the datepart to suit your needs. You can round @Step for legibility, either up or down.

Read the jobs that overlap the interval. For those that start before or end after set the effective values to the interval's limits. Each job will have two parts - the blank portion before it starts and the execution time. The blank portion after it ends need not be processed but just left blank. The "before" may be zero length. The execution time must take at least one character so it shows up on the output. Use REPLICATE to construct these portions. The "before" length is DATEDIFF(MINUTE, @IntervalStart, JobStart) / @Step, the "execution" is DATEDIFF(MINUTE, JobStart, JobEnd) / @Step with a minimum of 1.

I had an output table #Report(RowNumber int identity(1, 1), Data varchar(115)). Row number is required to ensure ordering is preserved.

Populate #Report from top to bottom. First column headers and @IntervalStart and @IntervalEnd proportionately positioned using @Step. Other time markers help and are easy if @Step was rounded to, say, 5 minutes. Then add each job in alphabetical or start time sequence as your requirements dictate. Concatenate the name, separator, before & execution and after portions into #Report.Data. Finally output #Report ordered by RowNumber to CSV or screen. SSMS is particularly useful for this. With a bit of fiddling you can get this:

               2020-05-20                              2020-05-20
Job            00:00         00:10           00:20           00:30
-------------  +---------------+---------------+---------------+
Backup         *******************
CheckDB                           *********************
Restore Dev                       **********************
Mask PII                                                ********
Send email                                              *
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.