Not managing your backup schedules could cause you several problems later on. This is especially the case when you do not have any centralized backup management software, such as Red Gate's SQL Backup. Managing backups is fundamental for a database administrator, but I believe revisiting the basics is good practice, especially of your are a new DBA.
For the past several months, I have noticed the duration of several backups taking up to 50% longer. At first I just assumed since most of these databases are growing, it makes sense the backups would take longer too, so I did not do anything about it. Eventually, I noticed most of these databases had grown only about 15% over the same period. This did not add up to me. I then looked at my backup schedules on the the different servers and noticed that some of them started at the same time and some where not completing before the next one started. These jobs where competing for SQL Server resources, primarily disk contention on the local backup drive as well as the the network drive, where the backup file was later copied. There was also a developer database restore job that runs at the same time other backup jobs were running, causing disk contention on the network drive.
Of course, the answer is to just schedule the backups appropriately, but there is a lesson here. Here are a few things I do to keep my backup schedules from tripping on each other:
- Periodically review your backup schedules. If your backup jobs use the same drive, local or network, plan your schedules to work with all jobs on all servers.
- Consider other jobs or processes that will be accessing the same drive.
- Monitor your database growth. Your backups will grow too.
- Monitor your backup duration and adjust your schedules accordingly.
- Use a spreadsheet to list all your schedules' for all servers to see if any overlap. List Server Name, Process/Job Name, Start Time, Average Duration, and Average End Time.
