Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to create a view for a report so that I can easily keep track of all of the backups that I have scheduled on a server. All of these backups are scheduled in subplans of a maintenance plan, so the job names all start with "Backup.".

The problem that I've run into is that I want to easily be able to see when the job is scheduled to run again and how often it's scheduled. The sysschedules table has the fields freq_type and freq_interval. The definition of their values can be found here. As you can see these fields are, for lack of a better way to describe, accumulative. I have no idea on how to, in a set-based solution, set it up so that I can see the names of the day.

What'd I'd like to be able to see is something like "Executes every 3 days" or "Executes every monday, tuesday, friday".

How can I write this so that I'm not using layers of case statements to get what I want?

Here's my code so far:

from msdb.dbo.sysjobs j
    left outer join msdb.dbo.sysjobschedules s
        on j.job_id=s.job_id
    left outer join msdb.dbo.sysschedules ss
        on s.schedule_id=ss.schedule_id
where like 'backup.%'
share|improve this question
up vote 3 down vote accepted

There is really no other way than to use a case statement, if you want the plain English version of all the integer values used by SQL Server (at least to my limited knowledge of T-SQL). This is a common thing needed by alot of DBAs and there are plenty of scripts out there on the subject. I found the script from Michelle Ufford to be quite useful in getting a good view of the schedule of jobs running. Since maintenance plans end up as SQL Agent jobs this will show you that as well.

The output you get from the script will give you the frequency, subFrequency, scheduled time and next run time and date. I just took her script and put it as a stored procedure on my server so I can easily run it when needed. You could also put it as a view if you wanted too. Since other folks can come behind me and add jobs and such I like to check it every-now-and-then to see what is going on.

share|improve this answer

This PowerShell script will list jobs on a local or remote SQL Server, along with a human readable interpretation of their schedule. It uses internal SSMS code to format the schedule, so you must have it installed on the machine you run the script on.

#requires -modules sqlps
#requires -version 3

param([string]$ServerToQuery = ([Environment]::MachineName), [PSCredential]$SqlCredential)

$ErrorActionPreference = 'Stop'

$ssmsAssembly = ls -r 'C:\Program Files (x86)\Microsoft SQL Server' -fi sqlmgmt.dll | sort FullName -Descending | select -First 1 -ExpandProperty FullName
Add-Type -Path $ssmsAssembly

$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $ServerToQuery
if($SqlCredential) {
    $server.ConnectionContext.LoginSecure = $false
    $server.ConnectionContext.Login = $SqlCredential.UserName
    $server.ConnectionContext.SecurePassword = $SqlCredential.Password

$server.JobServer.Jobs | % {
    $job = $_
    $schedules = $_.JobSchedules
    $jobSchedules = $schedules | % {
        $schedule = $_
        $jobScheduleData = New-Object Microsoft.SqlServer.Management.SqlMgmt.JobScheduleData -ArgumentList $_

    New-Object psobject -Property ([ordered]@{ Name = $job.Name; Schedules = $jobSchedules -join ', ' })

Sample output:

Name                                 Schedules
----                                 ---------
ASPState_Job_DeleteExpiredSessions   Occurs every day every 1 minute(s) between 00:00:00 and 23:59:59. Schedule will be used starting on 16/10/2000.
syspolicy_purge_history              Occurs every day at 02:00:00. Schedule will be used starting on 1/01/2008.

Suggested usage:

.\GetSchedules.ps1 | Out-GridView
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.