Очистка истории выполнения джобов

Административные скрипты MS SQL > Очистка истории выполнения джобов
13.12.2018 22:50:10



Статья:

DECLARE @freq_subtype1 int = 1; -- джоб запускается в указанное время
DECLARE @freq_subtype2 int = 2; -- интервал запуска джоба измеряется секундах
DECLARE @freq_subtype4 int = 4; -- интервал запуска джоба измеряется минутах
DECLARE @freq_subtype8 int = 8; -- интервал запуска джоба измеряется часах
 
DECLARE @max_rows_in_history0 int = 200 -- максимальное количество запусков джоба хранящихся в истории для джобов без запусков (schedules)
DECLARE @max_rows_in_history1 int = 40 -- максимальное количество запусков джоба хранящихся в истории для @freq_subtype1
DECLARE @max_rows_in_history2 int = 300 -- максимальное количество запусков джоба хранящихся в истории для @freq_subtype2
DECLARE @max_rows_in_history4 int = 200 -- максимальное количество запусков джоба хранящихся в истории для @freq_subtype4
DECLARE @max_rows_in_history8 int = 200 -- максимальное количество запусков джоба хранящихся в истории для @freq_subtype8
 
DELETE jh
FROM
    msdb.dbo.sysjobhistory jh 
    INNER JOIN 
    (
        SELECT 
            job_id
            , max(instance_id) as instanse_id
        FROM
            (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY job_id ORDER By run_date DESC, run_time DESC) as ROW_NUM
                , instance_id
                , job_id
            --    , *
            FROM
                msdb.dbo.sysjobhistory jh WITH(NOLOCK) 
            WHERE
                EXISTS (
                    SELECT 
                        * 
                    FROM 
                        (
                        SELECT 
                        j.job_id
                        , min(freq_type) as min_freq_type
                        , min(freq_interval) as min_freq_interval
                        , min(freq_subday_type) as min_freq_subday_type
                        , min(freq_subday_interval) as min_freq_subday_interval
                        , min(freq_relative_interval) as min_freq_relative_interval
                        , min(freq_recurrence_factor) as min_freq_recurrence_factor
                        FROM 
                            [msdb].[dbo].[sysjobs] j WITH (NOLOCk) 
                            LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] sjs WITH (NOLOCk)  ON (j.job_id = sjs.job_id)
                            LEFT OUTER JOIN [msdb].[dbo].[sysschedules] sch WITH (NOLOCk)  ON (sjs.schedule_id = sch.schedule_id) 
                        WHERE 
                            j.enabled = 1 
                            AND sch.enabled = 1
                        GROUP BY j.job_id, j.name
                        ) T
                        WHERE 
                            min_freq_subday_type = @freq_subtype1
                            AND jh.job_id = T.job_id
                    )
                    AND step_id = 0
            ) T2
        WHERE 
            ROW_NUM > @max_rows_in_history1
        GROUP BY job_id
    ) T3
        ON (jh.job_id = T3.job_id AND jh.instance_id <= T3.instanse_id)
 
PRINT ''Указанное время '' + CAST(@@ROWCOUNT AS VARCHAR(10))
 
DELETE jh
FROM
    msdb.dbo.sysjobhistory jh 
    INNER JOIN 
    (
        SELECT 
            job_id
            , max(instance_id) as instanse_id
        FROM
            (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY job_id ORDER By run_date DESC, run_time DESC) as ROW_NUM
                , instance_id
                , job_id
            --    , *
            FROM
                msdb.dbo.sysjobhistory jh WITH(NOLOCK) 
            WHERE
                EXISTS (
                    SELECT 
                        * 
                    FROM 
                        (
                        SELECT 
                        j.job_id
                        , min(freq_type) as min_freq_type
                        , min(freq_interval) as min_freq_interval
                        , min(freq_subday_type) as min_freq_subday_type
                        , min(freq_subday_interval) as min_freq_subday_interval
                        , min(freq_relative_interval) as min_freq_relative_interval
                        , min(freq_recurrence_factor) as min_freq_recurrence_factor
                        FROM 
                            [msdb].[dbo].[sysjobs] j WITH (NOLOCk) 
                            LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] sjs WITH (NOLOCk)  ON (j.job_id = sjs.job_id)
                            LEFT OUTER JOIN [msdb].[dbo].[sysschedules] sch WITH (NOLOCk)  ON (sjs.schedule_id = sch.schedule_id) 
                        WHERE 
                            j.enabled = 1 
                            AND sch.enabled = 1
                        GROUP BY j.job_id, j.name
                        ) T
                        WHERE 
                            min_freq_subday_type = @freq_subtype2
                            AND jh.job_id = T.job_id
                    )
                    AND step_id = 0
            ) T2
        WHERE 
            ROW_NUM > @max_rows_in_history2
        GROUP BY job_id
    ) T3
        ON (jh.job_id = T3.job_id AND jh.instance_id <= T3.instanse_id)
 
PRINT ''Секундные перерывы '' + CAST(@@ROWCOUNT AS VARCHAR(10))
 
DELETE jh
FROM
    msdb.dbo.sysjobhistory jh 
    INNER JOIN 
    (
        SELECT 
            job_id
            , max(instance_id) as instanse_id
        FROM
            (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY job_id ORDER By run_date DESC, run_time DESC) as ROW_NUM
                , instance_id
                , job_id
            --    , *
            FROM
                msdb.dbo.sysjobhistory jh WITH(NOLOCK) 
            WHERE
                EXISTS (
                    SELECT 
                        * 
                    FROM 
                        (
                        SELECT 
                        j.job_id
                        , min(freq_type) as min_freq_type
                        , min(freq_interval) as min_freq_interval
                        , min(freq_subday_type) as min_freq_subday_type
                        , min(freq_subday_interval) as min_freq_subday_interval
                        , min(freq_relative_interval) as min_freq_relative_interval
                        , min(freq_recurrence_factor) as min_freq_recurrence_factor
                        FROM 
                            [msdb].[dbo].[sysjobs] j WITH (NOLOCk) 
                            LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] sjs WITH (NOLOCk)  ON (j.job_id = sjs.job_id)
                            LEFT OUTER JOIN [msdb].[dbo].[sysschedules] sch WITH (NOLOCk)  ON (sjs.schedule_id = sch.schedule_id) 
                        WHERE 
                            j.enabled = 1 
                            AND sch.enabled = 1
                        GROUP BY j.job_id, j.name
                        ) T
                        WHERE 
                            min_freq_subday_type = @freq_subtype4
                            AND jh.job_id = T.job_id
                    )
                    AND step_id = 0
            ) T2
        WHERE 
            ROW_NUM > @max_rows_in_history4
        GROUP BY job_id
    ) T3
        ON (jh.job_id = T3.job_id AND jh.instance_id <= T3.instanse_id)
 
PRINT ''Минутные перерывы '' + CAST(@@ROWCOUNT AS VARCHAR(10))
 
DELETE jh
FROM
    msdb.dbo.sysjobhistory jh 
    INNER JOIN 
    (
        SELECT 
            job_id
            , max(instance_id) as instanse_id
        FROM
            (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY job_id ORDER By run_date DESC, run_time DESC) as ROW_NUM
                , instance_id
                , job_id
            --    , *
            FROM
                msdb.dbo.sysjobhistory jh WITH(NOLOCK) 
            WHERE
                EXISTS (
                    SELECT 
                        * 
                    FROM 
                        (
                        SELECT 
                        j.job_id
                        , min(freq_type) as min_freq_type
                        , min(freq_interval) as min_freq_interval
                        , min(freq_subday_type) as min_freq_subday_type
                        , min(freq_subday_interval) as min_freq_subday_interval
                        , min(freq_relative_interval) as min_freq_relative_interval
                        , min(freq_recurrence_factor) as min_freq_recurrence_factor
                        FROM 
                            [msdb].[dbo].[sysjobs] j WITH (NOLOCk) 
                            LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] sjs WITH (NOLOCk)  ON (j.job_id = sjs.job_id)
                            LEFT OUTER JOIN [msdb].[dbo].[sysschedules] sch WITH (NOLOCk)  ON (sjs.schedule_id = sch.schedule_id) 
                        WHERE 
                            j.enabled = 1 
                            AND sch.enabled = 1
                        GROUP BY j.job_id, j.name
                        ) T
                        WHERE 
                            min_freq_subday_type = @freq_subtype8
                            AND jh.job_id = T.job_id
                    )
                    AND step_id = 0
            ) T2
        WHERE 
            ROW_NUM > @max_rows_in_history8
        GROUP BY job_id
    ) T3
        ON (jh.job_id = T3.job_id AND jh.instance_id <= T3.instanse_id)
 
PRINT ''Часовые перерывы '' + CAST(@@ROWCOUNT AS VARCHAR(10))
 
DELETE jh
FROM
    msdb.dbo.sysjobhistory jh 
    INNER JOIN 
    (
        SELECT 
            job_id
            , max(instance_id) as instanse_id
        FROM
            (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY job_id ORDER By run_date DESC, run_time DESC) as ROW_NUM
                , instance_id
                , job_id
            --    , *
            FROM
                msdb.dbo.sysjobhistory jh WITH(NOLOCK) 
            WHERE
                EXISTS (
                    SELECT 
                        * 
                    FROM 
                        (
                        SELECT 
                        j.job_id
                        , min(freq_type) as min_freq_type
                        , min(freq_interval) as min_freq_interval
                        , min(freq_subday_type) as min_freq_subday_type
                        , min(freq_subday_interval) as min_freq_subday_interval
                        , min(freq_relative_interval) as min_freq_relative_interval
                        , min(freq_recurrence_factor) as min_freq_recurrence_factor
                        FROM 
                            [msdb].[dbo].[sysjobs] j WITH (NOLOCk) 
                            LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] sjs WITH (NOLOCk)  ON (j.job_id = sjs.job_id)
                            LEFT OUTER JOIN [msdb].[dbo].[sysschedules] sch WITH (NOLOCk)  ON (sjs.schedule_id = sch.schedule_id) 
                        WHERE 
                            NOT(j.enabled = 1 
                            AND sch.enabled = 1)
                        GROUP BY j.job_id, j.name
                        ) T
                        WHERE 
                            jh.job_id = T.job_id
                    )
                    AND step_id = 0
            ) T2
        WHERE 
            ROW_NUM > @max_rows_in_history0
        GROUP BY job_id
    ) T3
        ON (jh.job_id = T3.job_id AND jh.instance_id <= T3.instanse_id)
 
PRINT ''Джобы без расписания '' + CAST(@@ROWCOUNT AS VARCHAR(10))