Проблема: Иногда мне поступают запросы на подсчет запусков заданий SQL Server Agent за прошедшую неделю, месяц, квартал или год. Однако я замечаю, что таблица sysjobhistory в базе данных msdb не всегда содержит исторические данные о запусках заданий для полученных запросов. Количество запусков заданий иногда меньше, чем должно быть, и недостаток обычно наиболее распространен, когда мне нужно подсчитать задания за более длительные периоды, задания с большим количеством шагов или задания, которые выполняются очень часто.
Решение: Таблица sysjobhistory в базе данных msdb является таблицей, которая содержит исторические данные о запусках заданий SQL Server Agent, такие как время их запуска, продолжительность выполнения и успешность выполнения. Эта информация сохраняется как на уровне отдельного шага задания, так и на уровне всего задания. Однако существуют настройки по умолчанию для ограничений на количество сохраняемой истории в таблице sysjobhistory. Кроме того, вы можете вручную или программно настроить ограничения на количество данных, которые могут быть сохранены в таблице sysjobhistory.
Для генерации действительных исторических данных о запусках заданий SQL Server Agent вам необходимо иметь рабочие знания о типе данных в таблице sysjobhistory, а также о том, как SQL Server позволяет вам управлять ограничениями на количество данных, хранящихся в таблице sysjobhistory. В этом блоге мы рассмотрим столбцы и строки таблицы sysjobhistory, обсудим ручные и программные способы установки ограничений на количество данных в таблице sysjobhistory и предоставим примеры кода, которые помогут вам сохранять исторические данные о запусках заданий SQL Server вне встроенных таблиц SQL Server Agent в случае особых требований к отслеживанию запусков заданий.
Понимание таблицы sysjobhistory
Таблица sysjobhistory хранит исторические данные о запусках заданий как на уровне отдельного шага задания, так и на уровне всего задания. Каждый запуск задания представлен набором строк в таблице sysjobhistory. Запуск задания с одним шагом будет иметь 2 строки в своем наборе – одну для его единственного шага и другую для всего задания. В общем случае размер набора строк для запуска задания будет на единицу больше количества выполненных шагов в задании. Если задание отключено и оно никогда не запускается по требованию, в таблице sysjobhistory не будет ни одной строки для этого задания.
Вот пример запроса, который извлекает выбранные значения столбцов из таблицы sysjobhistory:
SELECT name, sysjobhistory.instance_id, sysjobhistory.job_id, sysjobhistory.step_id, sysjobhistory.step_name, sysjobhistory.sql_message_id, sysjobhistory.sql_severity, sysjobhistory.run_status, sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration FROM msdb.dbo.sysjobs LEFT JOIN msdb.dbo.sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id ORDER BY name, instance_id
Таблица sysjobhistory имеет несколько столбцов, которые предоставляют информацию о запусках заданий, такую как имя задания, имя шага, идентификатор сообщения SQL, серьезность SQL, статус выполнения, дата выполнения, время выполнения и продолжительность выполнения. Путем выполнения запросов к этой таблице вы можете получить исторические данные о запусках заданий и выполнять различные анализы.
Установка ограничений на историю запуска заданий
По умолчанию существуют ограничения на количество сохраняемой истории в таблице sysjobhistory. Ограничение по умолчанию на количество строк для каждого задания составляет 100, а ограничение по умолчанию на количество строк для всех заданий составляет 1000. Эти ограничения позволяют быстро открывать таблицу истории заданий, но они могут быть не подходящими для отслеживания заданий, которые выполняются часто и/или имеют много шагов или которые необходимо отслеживать в течение нескольких недель или даже месяцев.
Вы можете вручную изменить сохраняемую историю журнала в терминах количества строк или продолжительности во времени от сегодняшнего дня. Для этого щелкните правой кнопкой мыши на SQL Server Agent в Обозревателе объектов, выберите Свойства и перейдите на страницу История. Оттуда вы можете изменить ограничения для максимального размера журнала истории заданий (в строках) и максимального количества строк истории заданий на задание.
Если вы предпочитаете программный подход, вы можете использовать хранимую процедуру sp_set_sqlagent_properties для установки максимального размера журнала истории заданий и максимального количества строк истории заданий на задание. Кроме того, вы можете использовать хранимую процедуру sp_purge_jobhistory для очистки истории заданий на основе указанной даты. Это позволяет вам контролировать количество сохраняемой истории в таблице sysjobhistory в соответствии с вашими конкретными требованиями.
Подсчет запусков заданий
Помимо простого перечисления исторических данных о запусках заданий, вы можете использовать таблицу sysjobhistory для подсчета количества успешных и неуспешных запусков заданий. Это может предоставить ценную информацию о том, как используется экземпляр SQL Server и выявить проблемы, требующие внимания.
Вот пример запроса, который подсчитывает количество успешных и неуспешных запусков заданий для включенных запланированных заданий:
SELECT enabled_scheduled_jobs.name, ISNULL(success_jobs.number_of_success_runs, 0) AS number_of_success_runs, ISNULL(success_jobs.total_duration_for_success_runs_secs, 0) AS total_duration_for_success_runs_secs, ISNULL(not_success_jobs.number_of_not_success_runs, 0) AS number_of_not_success_runs, ISNULL(not_success_jobs.total_duration_for_not_success_runs_secs, 0) AS total_duration_for_not_success_runs_secs FROM ( SELECT DISTINCT sysjobs.job_id, name FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id WHERE enabled = 1 ) enabled_scheduled_jobs LEFT JOIN ( SELECT sysjobhistory.job_id, COUNT(*) AS number_of_success_runs, SUM(run_duration) AS total_duration_for_success_runs_secs FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_status = 1 GROUP BY sysjobhistory.job_id ) success_jobs ON enabled_scheduled_jobs.job_id = success_jobs.job_id LEFT JOIN ( SELECT sysjobhistory.job_id, COUNT(*) AS number_of_not_success_runs, SUM(run_duration) AS total_duration_for_not_success_runs_secs FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_status != 1 GROUP BY sysjobhistory.job_id ) not_success_jobs ON enabled_scheduled_jobs.job_id = not_success_jobs.job_id
Этот запрос предоставляет информацию о количестве успешных и неуспешных запусков заданий, а также о общей продолжительности для каждой категории. Анализируя эти данные, вы можете получить представление о производительности и надежности ваших заданий SQL Server Agent.
Создание внешней таблицы истории заданий
Если вам нужно хранить больше истории запуска заданий, чем позволяет таблица sysjobhistory, вы можете создать внешнюю таблицу истории заданий в другой базе данных. Это позволяет вам хранить столько истории, сколько вам нужно, не влияя на размер таблицы sysjobhistory.
Вот пример скрипта, который создает внешнюю таблицу истории заданий:
CREATE TABLE for_sql_server_agent_jobs.dbo.external_job_history( [name] [sysname] NOT NULL, [step_name] [sysname] NULL, [sql_message_id] [int] NULL, [sql_severity] [int] NULL, [run_status] [int] NULL, [run_date] [int] NULL, [run_time] [int] NULL, [run_duration] [int] NULL ) ON [PRIMARY]
Затем вы можете заполнять эту таблицу истории запуска заданий, периодически выполняя скрипт, подобный следующему:
INSERT INTO for_sql_server_agent_jobs.dbo.external_job_history SELECT name, sysjobhistory.step_name, sysjobhistory.sql_message_id, sysjobhistory.sql_severity, sysjobhistory.run_status, sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration FROM msdb.dbo.sysjobs LEFT JOIN msdb.dbo.sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id WHERE sysjobs.enabled = 1 AND sysjobhistory.run_date IS NOT NULL ORDER BY name, instance_id, run_date, run_time
Создав и заполнив внешнюю таблицу истории заданий, вы можете хранить и анализировать историю запуска заданий за пределами таблицы sysjobhistory.
Заключение
Отслеживание и подсчет запусков заданий SQL Server Agent является важной задачей для администраторов SQL Server. Понимая таблицу sysjobhistory и устанавливая соответствующие ограничения на историю запуска заданий, вы можете точно отслеживать и подсчитывать запуски заданий для различных требований. Кроме того, создав внешнюю таблицу истории заданий, вы можете хранить и анализировать историю запуска заданий за пределами таблицы sysjobhistory.
Следуя рекомендациям и используя предоставленные в этом блоге примеры кода, вы можете эффективно отслеживать и подсчитывать запуски заданий SQL Server Agent и получать ценную информацию о производительности и надежности вашей среды SQL Server.