Executive Summary:
Microsoft SQL Server Management Studio (SSMS) is great at quickly creating jobs and schedules. However, it's not so great at generating a master list of those jobs. Using Microsoft SQL Server Management Studio to generate a master job list involves a lot of mouse-clicking and cutting and pasting. Fortunately, Bill McEvoy has come up with an alternative: a T-SQL stored procedure named sp_ShowJobSchedules, which runs on Microsoft SQL Server 2005. |
If you’re a DBA who has just transferred to a new
environment, you should familiarize yourself with
the existing SQL Server scheduled jobs. Documenting those
job schedules can be an arduous task, especially in a large
production environment. Although you can use SQL Server
Management Studio (SSMS) to quickly create jobs and
schedules, generating a master list usually involves a lot of
mouse-clicking followed by a lot of cutting and pasting.
The
sp_ShowJobSchedules stored procedure changes all that.
The sp_ShowJobSchedules stored procedure generates
a master schedule for all jobs on the server. The report
generated includes information such as the server name,the job name, the schedule name, whether or not the job
is enabled, the frequency, and the interval. For readability,
the information is provided in plain English where possible.
Figure 3 shows some sample output that has been
condensed for space purposes.
I wrote sp_ShowJobSchedules for SQL Server 2005.
You can download this store procedure by going to www.sqlmag.com, entering 97793 in the InstantDoc ID text
box, then clicking the 97793.zip hotlink. I use sp_Show-
JobSchedules all the time. I hope you will, too.
