| Downloads |
|---|
| 97793.zip |
| 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.
