SQL Agent Job schedule details

SQL Agent Job schedule details

In this script I have attempted to output job name, job description (if any), job owner, job status, job date created, job date modified, job frequenct and job interval.

This script will all the jobs except the one that are created by Report Manager to report subscription. Also for the Job Interval, this will only output the value if they are present as per http://msdn.microsoft.com/en-us/library/ms178644%28v=sql.105%29.aspx.

I am only looking at following frequency types,

1) Daily

2) Weekly

3) Monthly, relative to freq_interval and

4) One time only

Please do make changes to suit your requirements.

USE msdb

GO

SELECT distinct

s.[name]

,s.[description]

,dbo.SQLAGENT_SUSER_SNAME (s.owner_sid) AS 'Job Owner'

--,sp.NAME AS 'JobOwner'

,s.[enabled]

,s.date_created

,s.date_modified

--,sjs.next_run_date

, JobFrequency = CASE freq_type

    WHEN 1 THEN 'One time only'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly, relative to freq_interval'

    WHEN 64 THEN 'Runs when the SQL Server Agent service starts'

    WHEN 128 THEN 'Runs when the computer is idle'

ELSE 'Unknown'

END

,JobInterval = CASE

    WHEN freq_type = 8 THEN

         CASE

            WHEN freq_interval = 1 THEN 'Sunday'

            WHEN freq_interval = 2 THEN 'Monday'

            WHEN freq_interval = 4 THEN 'Tuesday'

            WHEN freq_interval = 8 THEN 'Wednesday'

            WHEN freq_interval = 16 THEN 'Thursday'

            WHEN freq_interval = 32 THEN 'Friday'

            WHEN freq_interval = 64 THEN 'Saturday'

        ELSE

            CASE

                WHEN freq_type = 32 THEN

                    CASE

                        WHEN freq_interval = 1 THEN 'Sunday'

                        WHEN freq_interval = 2 THEN 'Monday'

                        WHEN freq_interval = 3 THEN 'Tuesday'

                        WHEN freq_interval = 4 THEN 'Wednesday'

                        WHEN freq_interval = 5 THEN 'Thursday'

                        WHEN freq_interval = 6 THEN 'Friday'

                        WHEN freq_interval = 7 THEN 'Saturday'

                        WHEN freq_interval = 8 THEN 'Day'

                        WHEN freq_interval = 9 THEN 'Weekday'

                        WHEN freq_interval = 10 THEN 'Weekend day'

        ELSE

            CASE

                WHEN freq_type = 1 THEN 'Once'

        ELSE

            CASE

                WHEN freq_type = 4 THEN 'Everyday'

        ELSE 'Unknown'

END

END

END

END

END

END

FROM dbo.sysjobs s

LEFT JOIN dbo.sysjobschedules sjs ON s.job_id = sjs.job_id

LEFT JOIN MASTER.sys.server_principals sp ON s.owner_sid = sp.sid

LEFT JOIN sysschedules ss on sjs.schedule_id = ss.schedule_id

WHERE description NOT LIKE 'This job is owned by a report server%'