Data Platform MVP Ola Hallengren has created and maintained his free SQL Server Maintenance Solution script for over eleven years now. His script creates some objects in your master system database (by default). It also creates and enables twelve SQL Server Agent jobs. These jobs do things like database backups, index maintenance, and database integrity checks. It is a great solution that many SQLskills.com clients use. In order to have the jobs run, you will need to create SQL Server Agent job schedules for each one.
Creating the Schedules
An enabled SQL Server Agent job that does not have a schedule associated with it will never automatically run. I frequently help clients setup and configure the Ola Hallengren SQL Server Maintenance Solution. After many years, I finally got tired of manually creating a job schedule for each of the SQL Server Agent jobs. I decided to create a T-SQL script that you can run to create a schedule for each of the twelve jobs, which you can get here.
Modifying the Schedules
You can (and probably should) modify the schedules in my script to suit your business requirements and infrastructure. For example, depending on your Recovery Point Objective (RPO) SLA, you would probably want to change how often you run transaction log backups. Another example is deciding when to run resource intensive jobs. These would the “DatabaseIntegrityCheck – USER_DATABASES” job or the “IndexOptimize – USER_DATABASES” job.
If you have you have shared storage, you would want to be more careful about your job scheduling. In that case, you will want to ensure that every instance is not doing resource intensive jobs at the same time. Please let me know what you think of this script and if you have any suggestions for improvements. Thanks!
3 thoughts on “Creating SQL Server Agent Job Schedules for Ola Hallengren’s Maintenance Solution”
Thank you so much Glenn! I’ve been thinking about the same for some time now, but never got myself to do it either. I can cross this off my to-do list now. Thanks again!
Thank you so much Glenn! I’ve been thinking about the same for some time now, but never got myself to do it. I can cross this off my to-do list now. Thanks again!
Glad you found it useful. Thanks!