Visualize your backup schedules

Recently I was asked by a customer to provide the database backup timings for every SQL Server (only the full backups).
Originally, all the backups were taken at 19:00 on a SAN disk that was attach to each server directly. Once the backup was taken, it is then pushed to tape every night. But recently the customer changed strategy and all backups had to be put on a CIFS share of a NetApp. The backup location was changed but not the schedule. As a result, some servers were having problem with the backups. All servers were taking their backups at the same time and were transferred over the network to the same network share. The network load increased a lot J

So, I had to come up with a solution to get the start time and the duration of each backup job of each server. With that information, I could reschedule the jobs on each server so that the network load was spread during the night.

This blog post of Brad Schulz (Blog) helped me to get the information out of the sysjobs tables. I’ve changed the script a bit so that I got the average backup duration of each server. I’m using a Central Management Server so it was easy to get my information in one time.

SET NOCOUNT ON; 
declare @HistoryStartDate datetime,
		@HistoryEndDate datetime

set @HistoryStartDate=’19000101’
set	@HistoryEndDate = getdate();

with JobHistData as(  
	select j.name,
			date_executed=convert(datetime,convert(varchar(8),h.run_date)) +’ ‘+stuff(stuff(right(1000000+h.run_time,6),3,0,’:’),6,0,’:’),
			jobsecsduration=h.run_duration/10000*3600+h.run_duration%10000/100*60+h.run_duration%100,
			jobtimeduration=stuff(stuff(right(1000000+h.run_duration,6),3,0,’:’),6,0,’:’),
			jobstarttime=stuff(stuff(right(1000000+s.active_start_time,6),3,0,’:’),6,0,’:’)
	from msdb.dbo.sysjobhistory  h
	inner join msdb..sysjobs j on h.job_id = j.job_id
	inner join msdb..sysjobschedules js on js.job_id=j.job_id
	inner join msdb..sysschedules s on s.schedule_id=js.schedule_id
	where j.name=’DatabaseBackup – USER_DATABASES – FULL’
	and step_id=0     --Job Outcome
	and run_status=1  --Succeeded
	)

select Name,AvgDuration=convert(int,avg(jobsecsduration*1)),CONVERT(VARCHAR, DATEADD(second,convert(int,avg(jobsecsduration*1)),0),108) as AvgTime,JobStarttime
from JobHistData 
where date_executed>=dateadd(day,datediff(day,’19000101’,@HistoryStartDate),’19000101’)  and date_executed<dateadd(day,1+datediff(day,’19000101’,@HistoryEndDate),’19000101’)
group by Name,jobstarttime

The query gives me the following result (server names are hidden)

Nice overview but not really useful to reschedule the backup jobs. I needed something visual, with timelines, so that I could easily see which backups were running at the same time. I tried several programs but I came up with a solution in… (ow yes) Excel! To be honest, I’m not an expert in Excel 😉

First I paste the result of my query in Excel. Column C and D should have type Time.
I added some extra columns:

  • Current Endtime = Column C + D
  • Number Starttime = Column D but the type is Number
  • Number Endtime = Column E but the type is Number
  • Number Elapsed Time = Column G – F. Column should also have the Number type

On the second sheet add an empty Stacked Bar Chart

Right click on the chart and select “Select Data…”

The Chart data range should be the first column Server Name
Edit the Series:

  • Change the Series name in Starttime
  • Series values will be column D Current Starttime

Add a second Serie

  • Change the Series name in Duration
  • Series values will be column H Number Elapsed Time

Edit the Category

  • Values will be column A Server Name

You should now have a similar configuration

Now that we have all the data in our chart, let’s fix the layout

Right click on the Horizontal Axis and select Format Axis…

On your right, select the AXIS Options and change the following values

  • Bound Minimum    0,791666666666667    This is 19:00, the start time of my backups
  • Bound Maximum    1            This is 00:00
  • Units Major        0,010416666666666    This is every 15 minutes

As a result, the axis should now be a time value from 19:00:00 till 00:00:00 for every 15 minutes.

Right click on the Vertical Axis, go to AXIS Options and select Categories in reverse order. This will change the order of my server list and will put the horizontal axis on top.

The last thing that you need to do is to hide the Starttime series. We are only interested in duration of the backup starting from the start time. In the chart, right click the starttime series (this is normally the blue line) and select Format Data Series Go to the Fill option and select No fill

Now you should get a similar result like this

If you want, you can add some extra design to the chart. I finally came up with the following layout

This really gives me a nice overview how my backups are scheduled.
Setting up my new backup timings will be really easy now. I just have to change the start time on the first sheet.

You can get my Excel example here!

Have fun!

Leave a Reply

Your email address will not be published. Required fields are marked *