Lors d’un audit de serveur, il est parfois utile de visualiser rapidement le planning d’exécution des jobs SQL Server. Si les jobs sont peu nombreux, il est simple d’aller voir directement dans les propriétés des jobs depuis la console SSMS mais lorsque le nombre de jobs augmente et devient important cette manipulation devient très facilement fastidieuse.

Voici donc un script qui permet de voir rapidement ce planning, avec la possibilité de retrouver les jobs qui s’exécutent dans une fenêtre de maintenance que vous aurez défini. Ce script fonctionne avec SQL Server 2005 et SQL Server 2008.

USE msdb;
GO

DECLARE @heureDebMaintenance INT
DECLARE @heureFinMaintenance INT

SET @heureDebMaintenance = 20 — Heure de début de la fenêtre de maintenance
SET @heureFinMaintenance = 8 — Heure de fin de la fenêtre de maintenance

SELECT
    s.name,
    CAST(sch.active_start_time / 10000 AS VARCHAR(10))
    + ‘:’ + RIGHT(’00’ + CAST(sch.active_start_time % 10000 / 100 AS VARCHAR(10)), 2)
     AS start_time,
    CASE
        WHEN (sch.active_start_time / 10000 >= @heureDebMaintenance)
              AND (sch.active_start_time / 10000 < 24)
               OR (sch.active_start_time / 10000 < @heureFinMaintenance) THEN ‘X’
        ELSE  »
    END AS maintenance,
    CASE sch.freq_type
        WHEN 4 THEN ‘X’
        ELSE  »
    END AS quotidien,
    CASE sch.freq_type
        WHEN 8 THEN ‘X’
        ELSE  »
    END AS hebdomadaire,
    CASE sch.freq_type
        WHEN 16 THEN ‘X’
        WHEN 32 THEN ‘X’
        ELSE  »
    END AS mensuel,
    CASE sch.freq_type
        WHEN 1 THEN ‘Une seule fois’
        WHEN 4 THEN ‘Tous les jours’
        WHEN 8 THEN CASE
                        WHEN freq_recurrence_factor = 1 THEN ‘Chaque semaine le ‘
                        WHEN freq_recurrence_factor > 1 THEN ‘Tous les ‘
                          + CAST(freq_recurrence_factor AS VARCHAR(2))
                          + ‘ semaines le(s) ‘
                    END
                    + LEFT(CASE WHEN sch.freq_interval &  1 =  1 THEN ‘Dimanche, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval &  2 =  2 THEN ‘Lundi, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval &  4 =  4 THEN ‘Mardi, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval &  8 =  8 THEN ‘Mercredi, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval & 16 = 16 THEN ‘Jeudi, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval & 32 = 32 THEN ‘Vendredi, ‘ ELSE  » END
                            + CASE WHEN sch.freq_interval & 64 = 64 THEN ‘Samedi, ‘ ELSE  » END
                          ,LEN(CASE WHEN sch.freq_interval &  1 =  1 THEN ‘Dimanche, ‘ ELSE  » END
                                + CASE WHEN sch.freq_interval &  2 =  2 THEN ‘Lundi, ‘ ELSE  » END
                                + CASE WHEN sch.freq_interval &  4 =  4 THEN ‘Mardi, ‘ ELSE  » END
                                + CASE WHEN sch.freq_interval &  8 =  8 THEN ‘Mercredi, ‘ ELSE  » END
                                + CASE WHEN sch.freq_interval & 16 = 16 THEN ‘Jeudi, ‘  ELSE  » END
                                + CASE WHEN sch.freq_interval & 32 = 32 THEN ‘Vendredi, ‘ ELSE  » END
                                + CASE WHEN sch.freq_interval & 64 = 64 THEN ‘Samedi, ‘ ELSE  » END
                               ) – 1 
                         )       
        WHEN 16 THEN ‘Tous les ‘ + CAST(sch.freq_interval AS VARCHAR(2))
                     + ‘ de chaque ‘ + CAST(sch.freq_recurrence_factor AS VARCHAR(2)) + ‘ mois’
        WHEN 32 THEN CASE sch.freq_relative_interval
                        WHEN 1 THEN ‘Le premier ‘
                        WHEN 2 THEN ‘Le deuxième ‘
                        WHEN 4 THEN ‘Le troisième ‘
                        WHEN 8 THEN ‘Le quatrième ‘
                        WHEN 16 THEN ‘Le dernier ‘
                     END
                     + CASE sch.freq_interval
                        WHEN 1 THEN ‘dimanche’
                        WHEN 2 THEN ‘lundi’
                        WHEN 3 THEN ‘mardi’
                        WHEN 4 THEN ‘mercredi’
                        WHEN 5 THEN ‘jeudi’
                        WHEN 6 THEN ‘vendredi’
                        WHEN 7 THEN ‘samedi’
                        WHEN 8 THEN ‘jour’
                        WHEN 9 THEN ‘jour de la semaine’
                        ELSE ‘jour du week end’
                       END
                     + ‘ de chaque ‘ + CAST(sch.freq_recurrence_factor AS VARCHAR(2)) + ‘ mois’
        WHEN 64 THEN ‘A chaque redémarrage du service SQL Server Agent’
        ELSE ‘S »exécute lorsque l »ordinateur est inactif’
    END AS frequence_execution,
    CASE sch.freq_type
        WHEN 1 THEN  »
        WHEN 64 THEN  »
        WHEN 128 THEN  »
        ELSE CASE sch.freq_subday_type
                WHEN 1 THEN ‘Une fois à ‘ + RIGHT(’00’ + CAST(sch.active_start_time / 10000 AS VARCHAR(2)),2)                                              
                            + ‘:’ + RIGHT(’00’ + CAST(sch.active_start_time % 10000 / 100 AS VARCHAR(10)), 2)
                WHEN 2 THEN ‘Toutes les ‘ + CAST(sch.freq_subday_interval AS VARCHAR(2)) + ‘ seconde(s)’
                WHEN 4 THEN ‘Toutes les ‘ + RIGHT(’00’ + CAST(sch.freq_subday_interval AS VARCHAR(2)),2) + ‘ minute(s)’
                ELSE ‘Toutes les ‘ + CAST(sch.freq_subday_interval AS VARCHAR(2)) + ‘ heure(s)’
             END
        END AS frequence_execution_jour,
    CASE sch.freq_subday_type
        WHEN 1 THEN  »
        ELSE ‘De ‘ + RIGHT(’00’ + CAST(sch.active_start_time / 10000 AS VARCHAR(2)),2)
             + ‘:’ + RIGHT(’00’ + CAST(sch.active_start_time % 10000 / 100 AS VARCHAR(10)), 2)
             + ‘ à ‘ 
             + RIGHT(’00’ + CAST(sch.active_end_time / 10000 AS VARCHAR(10)),2)
             + ‘:’ + RIGHT(’00’ + CAST(sch.active_end_time % 10000 / 100 AS VARCHAR(10)), 2)
    END AS intervalle_execution
FROM dbo.sysjobs s
INNER JOIN dbo.sysjobschedules jsch
ON s.job_id = jsch.job_id
INNER JOIN dbo.sysschedules sch
ON jsch.schedule_id = sch.schedule_id 
WHERE s.enabled = 1 — Job actifs
ORDER BY sch.active_start_time ASC

Bon audit de planning !!!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon