Take the 2-minute tour ×
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.

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:

select 
    j.job_id,
    j.name,
    s.next_run_date,
    s.next_run_time,
    ss.*
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 j.name like 'backup.%'
share|improve this question
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.