Джобы в sql что это

3.5. SQL Server Agent — Часть 1

В состав сервера MS SQL Server входит сервис SQL Server Agent, который состоит из сообщений, операторов и работ. Наибольший интерес программистов и администраторов вызывают работы, поэтому этой теме мы уделим достаточно подробное внимание.

Работа администратора очень часто связана с выполнением однообразных задач, что превращает рабочий день в серые будни. Для меня это самое сложное, поэтому многократно выполняемые задачи я стремлюсь автоматизировать. У MS SQL Server есть достаточно мощное средство автоматизации – работ (job). Работы – это набор определенных действий (например, SQL запросов), которые могут выполняться сервером автоматически в определенное время с помощью планировщика (Schedule) или запускаться администратором вручную.

Ярким примером задач администратора, которые могут вызывать скуку, является обслуживание баз данных, о чем мы будем достаточно много говорить в главе 4. Например, можно запрограммировать сервер так, чтобы он каждый день в конце рабочего дня создавал резервную копию базы данных.

Работы состоят из шагов, которые последовательно выполняются сервером MS SQL Server. Выполнение каждого последующего шага может зависеть от результата предыдущего. Таким образом, можно строить определенную логику задач.

Вы должны учитывать, что работы выполняются не самим сервисом MS SQL Server, а сервисом SQL Server Agent, который входит в поставку MS SQL Server. Поэтому, убедитесь, что этот сервис работает, иначе работы не смогут выполняться по расписанию.

Помимо этого, если сервис обращается к удаленным серверам по сети, то SQL Server Agent должен работать под реальной учетной записью, а не под системной. Чтобы изменить имя пользователя, с правами, которыми работает сервис, запустите оснастку Сервисы (Пуск/Панель управления/Администрирование/Сервисы). Перед вами откроется окно, как на рисунке 3.1. Найдите строку с именем сервиса SQLSERVERAGENT и дважды щелкните по ней. Перейдите на закладку «Вход в систему» (Log on) и укажите реальную учетную запись пользователя, который существует в системе и обладает правами на необходимые ресурсы вашего компьютера и удаленного сервера, к которому будет происходить подключение по сети. Если сервис SQL Server Agent будет работать с правами системного аккаунта, то у него не хватит прав на подключение к удаленной системе, потому что системный аккаунт не имеет имени пользователя и пароля, необходимых для аутентификации.

3.5.1. Добавление работы

Начнем с добавления записей. Для этого используется хранимая процедура sp_add_job, которая выглядит следующим образом:

sp_add_job [ @job_name = ] 'job_name' [ , [ @enabled = ] enabled ] [ , [ @description = ] 'description' ] [ , [ @start_step_id = ] step_id ] [ , [ @category_name = ] 'category' ] [ , [ @category_id = ] category_id ] [ , [ @owner_login_name = ] 'login' ] [ , [ @notify_level_eventlog = ] eventlog_level ] [ , [ @notify_level_email = ] email_level ] [ , [ @notify_level_netsend = ] netsend_level ] [ , [ @notify_level_page = ] page_level ] [ , [ @notify_email_operator_name = ] 'email_name' ] [ , [ @notify_netsend_operator_name = ] 'netsend_name' ] [ , [ @notify_page_operator_name = ] 'page_name' ] [ , [ @delete_level = ] delete_level ] [ , [ @job_id = ] job_id OUTPUT ]

Рассмотрим параметры, которые передаются данной процедуре:

@job_name – имя работы, которое должно быть уникальным и не может содержать символ процента;
@enabled – индикатор активности работы. Если параметр равен 1, то работа активна и может выполняться из планировщика задач. Если указать 0, то работа не может выполняться автоматически, запуск доступен только вручную;
@description – текстовое описание работы;
@start_step_id – идентификатор работы, которая должна выполняться первой, по умолчанию используется число 1;
@category_name – имя категории;
@category_id – идентификатор категории. Этот параметр может использоваться в определенных языках программирования;
@owner_login_name – имя пользователя, который будет являться владельцем работы. Если ничего не указано, то владельцем будет текущий пользователь;

@notify_level_eventlog – какие сообщения должны сохраняться в журнале событий Windows. Здесь можно указать одно из следующих значений:

0 – никакие сообщения;
1 – сообщения об удачном завершении;
2 – сообщения об ошибках (значение по умолчанию);
3 – все сообщения.

Прежде чем использовать процедуру, необходимо отметить, что она принадлежит базе данных msdb, поэтому необходимо подключиться именно к этой базе.

Есть еще одно ограничение – вы должны указывать имена только реально существующих в базе данных операторов, поэтому посмотрим на пример без указания операторов:

USE msdb EXEC sp_add_job @job_name = 'Тестовая работа', @enabled = 1, @description = 'Это тестовая работа', @notify_level_eventlog = 3, @notify_level_email = 2, @notify_level_netsend = 1

3.5.2. Управление операторами

Оператор – это описание человека, который должен получать сообщения сервера MS SQL Server и сообщения о ходе выполнения работы. Для создания оператора используется процедура sp_add_operator, которая выглядит следующим образом:

sp_add_operator [ @name = ] 'name' [ , [ @enabled = ] enabled ] [ , [ @email_address = ] 'email_address' ] [ , [ @pager_address = ] 'pager_address' ] [ , [ @weekday_pager_start_time = ] weekday_pager_start_time ] [ , [ @weekday_pager_end_time = ] weekday_pager_end_time ] [ , [ @saturday_pager_start_time = ] saturday_pager_start_time ] [ , [ @saturday_pager_end_time = ] saturday_pager_end_time ] [ , [ @sunday_pager_start_time = ] sunday_pager_start_time ] [ , [ @sunday_pager_end_time = ] sunday_pager_end_time ] [ , [ @pager_days = ] pager_days ] [ , [ @netsend_address = ] 'netsend_address' ] [ , [ @category_name = ] 'category' ]

Рассмотрим параметры этой процедуры:

@name – имя оператора;
@enabled – если параметр равен 1, то оператор активен, а если указать 0, то оператор не доступен и не должен получать сообщений;
@email_address – электронный адрес (e-mail) оператора. Если значение этого параметра является физическим адресом SMTP:flenov@mail.ru, а не псевдонимом flenov, то он должен быть заключен в квадратные скобки [SMTP:flenov@mail.ru]. Дело в том, что по умолчанию MS SQL Server использует почтовый сервис Exchange Service, который использует свою систему именования ящиков;
@pager_address – адрес Интернет пейджера;
@weekday_pager_start_time – время, после которого можно отправлять сообщения на пейджер в рабочий день. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
@weekday_pager_end_time – время, до которого можно отправлять сообщения на пейджер в рабочий день. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
@saturday_pager_start_time и @sunday_pager_start_time – время, после которого можно отправлять сообщения на пейджер в субботу и в воскресенье соответственно. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
@saturday_pager_end_time и @sunday_pager_end_time – время, до которого можно отправлять сообщения на пейджер в субботу и в воскресенье соответственно. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;

@pager_days – этот параметр определяет дни, в которые оператор доступен для получения сообщений на пейджер. Здесь можно указать одно из следующих значений или сумму из нескольких чисел:

0 – никогда (это значение по умолчанию);
1 – по воскресеньям;
2 – по понедельникам;
4- по вторникам;
8 – по средам;
16- по четвергам;
32 – по пятницам;
64 – по субботам.

Как указать, что пользователь доступен с понедельника по пятницу? Для этого складываем соответствующие числа 2+4+8+16+32. В результате мы получим 62 и именно это значение необходимо указать в параметре @pager_days.

@netsend_address – сетевой адрес оператора (имя компьютера), на который нужно отправлять сообщения;
@category_name – имя категории сообщений.

Посмотрим, как можно создать оператора, который будет получать сообщения на e-mail адрес:

use msdb exec sp_add_operator @name = 'Андрей', @enabled = 1, @email_address ='[SMTP:flenov@mail.ru]'

Следующий пример создает оператора, который может получать e-mail и NET SEND сообщения:

use msdb exec sp_add_operator @name = 'Михаил', @enabled = 1, @email_address ='[SMTP:admin@mail.ru]', @netsend_address ='admincomp'

Теперь посмотрим, как можно создать работу, в которой сообщения о статусе выполнения работы передаются операторам:

USE msdb EXEC sp_add_job @job_name = 'Тестовая работа 2', @enabled = 1, @description = 'Это тестовая работа с указанием оператора', @notify_level_eventlog = 3, @notify_level_email = 2, @notify_level_netsend = 1, @notify_email_operator_name = 'Андрей', @notify_netsend_operator_name = 'Михаил'

Если хотя бы один из операторов, указанных в примере не будет существовать в базе данных, выполнение процедуры завершиться неудачей.

Изменение оператора

Для изменения параметров оператора используется процедура sp_update_operator, которая выглядит так:

sp_update_operator [@name =] 'name' [, [@new_name =] 'new_name'] [, [@enabled =] enabled] [, [@email_address =] 'email_address'] [, [@pager_address =] 'pager_number'] [, [@weekday_pager_start_time =] weekday_pager_start_time] [, [@weekday_pager_end_time =] weekday_pager_end_time] [, [@saturday_pager_start_time =] saturday_pager_start_time] [, [@saturday_pager_end_time =] saturday_pager_end_time] [, [@sunday_pager_start_time =] sunday_pager_start_time] [, [@sunday_pager_end_time =] sunday_pager_end_time] [, [@pager_days =] pager_days] [, [@netsend_address =] 'netsend_address'] [, [@category_name =] 'category']

Параметры процедуры изменения оператора такие же, как и при создании, поэтому не будем тратить время на рассмотрения оператора, а лучше посмотрим его работу на практике. Следующий пример изменяет e-mail и сетевой адрес:

exec sp_update_operator @name = 'Михаил', @email_address ='[SMTP:mikhail@mail.ru]', @netsend_address ='notebook'

Указание имени оператора является обязательным, потому что процедура должна знать, какого именно оператора нужно обновлять.

Следующий пример делает оператора не активным, после чего он не будет получать информационные сообщения:

exec sp_update_operator @name = 'Михаил', @enabled=0

Изменяется только параметр @enabled, а все остальные не изменяются и сохраняют свои значения.

Информация об операторе

Чтобы убедится в том, что изменения прошли успешно, можно воспользоваться процедурой sp_help_operator, которая выводит информацию об операторе. В качестве параметра @operator_name нужно передать имя интересующего вас оператора, например, так:

exec sp_help_operator @operator_name = 'Михаил'

Давайте снова сделаем Михаила активным, чтобы он мог получать информационные сообщения:

exec sp_update_operator @name = 'Михаил', @enabled=1

Удаление оператора

Для удаления оператора используется процедура sp_delete_operator, которая выглядит следующим образом:

sp_delete_operator [ @name = ] 'name' [ , [ @reassign_to_operator = ] 'reassign_operator' ]

Здесь всего два параметра:

@name – имя удаляемого оператора;
@reassign_to_operator – не обязательный параметр, где можно указать оператора, которому должны быть назначены события, отслеживаемые удаляемым оператором.

Следующий пример удаляет оператора Михаил, а все события, которые он отслеживал, будет теперь отслеживать Андрей:

EXEC sp_delete_operator @name = 'Михаил', @reassign_to_operator = 'Андрей'

3.5.3. Добавление шага

Мы научились создавать и удалять работу, а также добавлять операторов, но все это пока лишено смысла, ведь создаваемая работа еще ничего не умеет делать. Чтобы наделить смыслом предыдущие несколько страниц данной книги, необходимо научиться создавать шаги работы. Для этого используется процедура sp_add_jobstep. В общем виде процедура выглядит следующим образом:

sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name' [ , [ @step_id = ] step_id ] < , [ @step_name = ] 'step_name' >[ , [ @subsystem = ] 'subsystem' ] [ , [ @command = ] 'command' ] [ , [ @additional_parameters = ] 'parameters' ] [ , [ @cmdexec_success_code = ] code ] [ , [ @on_success_action = ] success_action ] [ , [ @on_success_step_id = ] success_step_id ] [ , [ @on_fail_action = ] fail_action ] [ , [ @on_fail_step_id = ] fail_step_id ] [ , [ @server = ] 'server' ] [ , [ @database_name = ] 'database' ] [ , [ @database_user_name = ] 'user' ] [ , [ @retry_attempts = ] retry_attempts ] [ , [ @retry_interval = ] retry_interval ] [ , [ @os_run_priority = ] run_priority ] [ , [ @output_file_name = ] 'file_name' ] [ , [ @flags = ] flags ]

В первой строке указано, что процедуре необходимо указать или идентификатор или имя работы, которой нужно добавить новый шаг. Давайте рассмотрим параметры более подробно:

@job_id – идентификатор работы, куда нужно добавить шаг;
@job_name – имя работы, которой нужно добавить шаг;
@step_id – автоматически увеличиваемое число, начиная с единицы. Если параметр не указан, то значение будет установлено автоматически (следующее за максимально существующим номером шага), т.е. шаг будет добавлен в конец цепочки шагов. Если у вас уже есть шаги с номерами 1, 2 и 3, и вы указали в параметре @step_id число 3, то новое значение будет вставлено на третью позицию, а шаг, который был на этом месте ранее, сместиться на четвертую. При этом, идентификаторы шагов будут перенумерованы автоматически;
@step_name – имя шага;

@subsystem – система команд, которые будут выполняться сервером на данном шаге. В этом параметре можно указать одно из следующих значений:

ACTIVESCRIPTING – выполняться должен активный сценарий;
CMDEXEC – на данном шаге будет выполняться команда ОС или внешняя программа;
DISTRIBUTION – выполнение дистрибутора репликации;
SNAPSHOT – выполнение агента репликации снятия снимка;
LOGREADER – выполнение агента чтения журнала;
MERGE – выполнение агента репликации смешивания (Merge);
TSQL – шаг будет выполнять команду Transact-SQL (это значение по умолчанию).

1 – выход с сообщением об удачном завершении. Если работе установлен оператор, отслеживающий удачное завершение работы, то он получит соответствующее уведомление;
2 – выход с ошибкой. Иногда удачное выполнение команды является отрицательным результатом. Например, запрос может проверять наличие несвязанных строк. Если строки найдены, то по идее шаг должен вернуть положительный результат, но не связанные строки – это нарушение целостности. Поэтому можно указать, что при удачном выполнении сценария, необходимо завершить работу с ошибкой и проинформировать оператора, если он установлен;
3 – перейти к выполнению следующего оператора;
4 – перейти к выполнению шага, указанного в параметре @on_success_step_id.

0 – нет никаких дополнительных опций (это значение по умолчанию);
2 – результат выполнения должен добавляться в выходной файл, указанный в параметре @output_file_name;
4 – перезаписать выходной файл. Существующее содержимое будет уничтожено.

Давайте добавим в работу с именем ‘Тестовая работа 2’ два шага. На первом шаге будет удаляться таблица tbAndrey, а на втором, эта же таблица будет создаваться с помощью оператора SELECT INTO. Создание первого шага для решения данной задачи может выглядеть примерно следующим образом:

EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_name = 'Удаляем таблицу', @subsystem = 'TSQL', @command = 'DROP TABLE tbAndrey', @database_name = 'FlenovSQLBook', @on_success_action = 3, @on_fail_action = 3

Данный шаг будет выполнять команду Transact-SQL, а значит, в параметре @subsystem указываем значение ‘TSQL’. В параметре @command указываем непосредственно SQL команду. Так как по умолчанию запрос будет выполняться в базе данных master, то в параметре @database_name явно указываем свою базу.

Основанная задача работы – создать таблицу tbAndrey и заполнить значениями, но для этого сначала старую таблицу нужно удалить. А что если старой таблицы нет (ее кто-то удалил или вообще ее небыло)? В этом случае все равно работа должна продолжать выполняться, поэтому в параметрах @on_success_action и @on_fail_action указываем значение 3, то есть переход на следующий шаг.

Теперь создадим второй шаг, на котором будет производиться создание таблицы:

EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_name = 'Выбираем Андреев', @subsystem = 'TSQL', @command = 'SELECT * INTO tbAndrey FROM tbPeoples WHERE vcName=''Андрей''', @database_name = 'FlenovSQLBook', @on_success_action = 1, @on_fail_action = 2

В данном случае переход на следующий шаг не ожидается, поэтому завершаем работу с соответствующим кодом.

Когда вы пишете сценарий для работы, вы можете использовать некоторые вспомогательные конструкции, которые во время выполнения будут заменяться на определенные параметры. Во как сказал! Рассмотрим возможные конструкции, и на что они заменяются:

[A-DBN] – заменяется именем базы данных;
[A-SVR] – заменяется именем сервера;
[A-ERR] – номер ошибки;
[A-SEV] – критичность ошибки;
[A-MSG] – заменяется текстовым сообщением;
[DATE] – текущая дата;
[JOBID] – идентификатор работы;
[MACH] – имя компьютера;
[MSSA] – имя сервиса SQLServerAgent;
[SQLDIR] – директория, в которую установлен SQL сервер;
[STEPCT] – сколько раз был запущена работа (количество попыток);
[STEPID] – идентификатор шага;
[TIME] – текущее время в формате HHMMSS;
[STRTTM] — время в формате HHMMSS, когда работа была запущена;
[STRTDT] – дата в формате YYYYMMDD когда работа была запущена.

Будьте внимательны, все конструкции должны заключаться в квадратные скобки, и все они чувствительны к регистру.

Давайте посмотрим, как использовать эти конструкции, а заодно увидим, как можно вставлять новые шаги. Следующим пример не добавляет новый шаг, а вставляет его на первую позицию (параметр @step_id равен 1):

EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_id=1, @step_name = 'Вставляем строку', @subsystem = 'TSQL', @command = 'INSERT INTO tbPeoples (vcName, vcSurname) VALUES(''[MACH]'', ''[STEPID]'')', @database_name = 'FlenovSQLBook', @on_success_action = 3, @on_fail_action = 3

В данном примере, в качестве команды в таблицу tbPeoples вставляется строка, в которой имени назначается имя компьютера (конструкция [MACH]), а в качестве фамилии указывается текущий номер шага (конструкция [STEPID]).

Обратите внимание, что в параметре @command, в SQL запросе в параметре VALUES вставляемые в таблицу значения должны быть в одинарных кавычках, а мы указали по две одинарных с каждой стороны. Почему? Дело в том, что вся команда INSERT должна быть в одинарных кавычках:

@command='КОМАНДА'

Если внутри команды используется одинарная кавычка, то сервер воспримет ее как конец команды, и он станет преждевременным. Например:

@command = 'INSERT INTO tbPeoples (vcName, vcSurname) VALUES('[MACH]', '[STEPID]')'

В данном случае, сервер поместит в параметр @command только строку:

@command = 'INSERT INTO tbPeoples (vcName, vcSurname) VALUES('

Именно этот текст находиться между первыми двумя кавычками. Чтобы этого не произошло, внутри команды нужно продублировать все одинарные кавычки, что и происходит в примере выше.

Автоматически заменяемые во время выполнения конструкции действительно могут упростить разработку работ и иногда оказываются незаменимыми, особенно даты и время выполнения работы.

Давайте создадим еще один шаг, на котором будет выполняться системная команда, и при этом этот шаг мы вставим под номером 2:

EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_id=2, @step_name = 'Системная команда', @subsystem = 'CMDEXEC', @command = 'del c:\text.txt', @on_success_action = 3, @on_fail_action = 3

Так как будет выполняться системная команда, параметр @subsystem устанавливаем в CMDEXEC. В параметре @command для примера я указал команду удаления файла text.txt из корня диска С:. Когда вы будете тестировать пример (о том, как это сделать мы узнаем в разделе 3.5.5), не забудьте создать этот файл, чтобы убедиться в том, что файл после выполнения работы исчезает.

При выполнении системных команд вы должны учитывать следующее:

Команды выполняются в контексте и с правами MS SQL Server. Это значит, что на экране не будет отображаться результат, даже если вы запускаете программу, которая должна что-то отображать на экране или должна иметь визуальный интерфейс. Чтобы убедиться в этом, можно в качестве системной команды указать calc (калькулятор). После запуска работы, вы ничего не увидите на экране;
Не запускайте в качестве команды программы с визуальным интерфейсом. Такие программы не видны на экране, поэтому их невозможно будет закрыть, а значит, работа просто зависнет и не сможет завершить работу;
Будьте аккуратны при задании команд, чтобы их выполнение было конечным, и не произошло зависания всей работы. Например, если задать команду ping -t localhost, то работа будет бесконечно пинговать локальный компьютер. Это приведет к зацикливанию шага и дальнейшее выполнение работы станет невозможным.

Автосбор данных о выполненных заданиях в MS SQL Server

Администратору баз данных важно знать, какие задачи выполнялись и каким образом это происходило (по длительности, успешно или не успешно и т. д.). Чтобы этого не делать вручную на каждом сервере, лучше данный процесс автоматизировать.

В данной статье приведу реализацию автоматического ежедневного сбора информации о выполненных заданиях Агента в MS SQL Server.

Решение

1) создать представление для отбора заданий:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vJobRunShortInfo] as SELECT sj.[job_id] as Job_GUID ,j.name as Job_Name ,case sj.[last_run_outcome] when 0 then 'Ошибка' when 1 then 'Успешно' when 3 then 'Отменено' else case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then 'Неопределенное состояние' else NULL end end as LastFinishRunState ,sj.[last_run_outcome] as LastRunOutcome ,case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then DATETIMEFROMPARTS( substring(cast(sj.[last_run_date] as nvarchar(255)),1,4), substring(cast(sj.[last_run_date] as nvarchar(255)),5,2), substring(cast(sj.[last_run_date] as nvarchar(255)),7,2), case when len(cast(sj.[last_run_time] as nvarchar(255)))>=5 then substring(cast(sj.[last_run_time] as nvarchar(255)),1,len(cast(sj.[last_run_time] as nvarchar(255)))-4) else 0 end, case when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))>=4 then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,2) when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))=3 then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,1) else 0 end, right(cast(sj.[last_run_duration] as nvarchar(255)),2), 0 ) else NULL end as LastDateTime ,case when len(cast(sj.[last_run_duration] as nvarchar(255)))>5 then substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4) when len(cast(sj.[last_run_duration] as nvarchar(255)))=5 then '0'+substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4) else '00' end +':' +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=4 then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,2) when len(cast(sj.[last_run_duration] as nvarchar(255)))=3 then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,1) else '00' end +':' +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=2 then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,2) when len(cast(sj.[last_run_duration] as nvarchar(255)))=2 then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,1) else '00' end as [LastRunDurationString] ,sj.last_run_duration as LastRunDurationInt ,sj.[last_outcome_message] as LastOutcomeMessage ,j.enabled as [Enabled] FROM [msdb].[dbo].[sysjobservers] as sj inner join msdb.dbo.sysjobs_view as j on j.job_id=sj.job_id; GO

Здесь используются два системных представления sysjobservers и sysjobs_view
2) создать таблицу для хранения отобранной информации:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[ShortInfoRunJobs]( [Job_GUID] [uniqueidentifier] NOT NULL, [Job_Name] [nvarchar](255) NOT NULL, [LastFinishRunState] [nvarchar](255) NULL, [LastDateTime] [datetime] NOT NULL, [LastRunDurationString] [nvarchar](255) NULL, [LastRunDurationInt] [int] NULL, [LastOutcomeMessage] [nvarchar](255) NULL, [LastRunOutcome] [tinyint] NOT NULL, [Server] [nvarchar](255) NOT NULL, [InsertUTCDate] [datetime] NOT NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_ShortInfoRunJobs] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[ShortInfoRunJobs] ADD CONSTRAINT [DF_ShortInfoRunJobs_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

3) создать в Агенте задачу и ежедневно собирать информацию о тех задачах, которые либо долго выполнялись (больше 30 сек.), либо завершились неудачно за последние 2 дня:

USE [ИМЯ_БАЗЫ_ДАННЫХ]; GO truncate table [srv].[ShortInfoRunJobs]; INSERT INTO [srv].[ShortInfoRunJobs] ([Job_GUID] ,[Job_Name] ,[LastFinishRunState] ,[LastDateTime] ,[LastRunDurationString] ,[LastRunDurationInt] ,[LastOutcomeMessage] ,[LastRunOutcome] ,[Server]) SELECT [Job_GUID] ,[Job_Name] ,[LastFinishRunState] ,[LastDateTime] ,[LastRunDurationString] ,[LastRunDurationInt] ,[LastOutcomeMessage] ,LastRunOutcome ,@@SERVERNAME FROM [srv].[vJobRunShortInfo] where [Enabled]=1 and ([LastRunOutcome]=0 or [LastRunDurationInt]>=30) and LastDateTime>=DateAdd(day,-2,getdate()); GO

Здесь же или в п.2 можно настроить фильтр, чтобы убрать ненужные задания. Например, связанные с репликацией, т. к. они работают долго
4) сформировать HTML-отчет для дальнейшей отправки на почту администраторам о результатах:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[GetHTMLTableShortInfoRunJobs] @body nvarchar(max) OUTPUT AS BEGIN /* формирует HTML-код для таблицы выполненных заданий */ SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @tbl table ( Job_GUID uniqueidentifier ,Job_Name nvarchar(255) ,LastFinishRunState nvarchar(255) ,LastDateTime datetime ,LastRunDurationString nvarchar(255) ,LastOutcomeMessage nvarchar(max) ,[Server] nvarchar(255) ,ID int identity(1,1) ); declare @Job_GUID uniqueidentifier ,@Job_Name nvarchar(255) ,@LastFinishRunState nvarchar(255) ,@LastDateTime datetime ,@LastRunDurationString nvarchar(255) ,@LastOutcomeMessage nvarchar(max) ,@Server nvarchar(255) ,@ID int; insert into @tbl( Job_GUID ,Job_Name ,LastFinishRunState ,LastDateTime ,LastRunDurationString ,LastOutcomeMessage ,[Server] ) select Job_GUID ,Job_Name ,LastFinishRunState ,LastDateTime ,LastRunDurationString ,LastOutcomeMessage ,[Server] from srv.ShortInfoRunJobs --order by LastRunDurationInt desc; if(exists(select top(1) 1 from @tbl)) begin set @body='В ходе анализа последних выполнений заданий, были выявлены следующие задания, которые либо с ошибочным завершением, либо выполнились по времени более 30 секунд:

'+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; while((select top 1 1 from @tbl)>0) begin set @body=@body+''; select top 1 @ID = [ID] ,@Job_GUID = Job_GUID ,@Job_Name = Job_Name ,@LastFinishRunState = LastFinishRunState ,@LastDateTime = LastDateTime ,@LastRunDurationString = LastRunDurationString ,@LastOutcomeMessage = LastOutcomeMessage ,@Server = [Server] from @tbl order by LastRunDurationInt desc; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; delete from @tbl where set @body=@body+''; end set @body=@body+''; set @body=@body+'№ п/п'; set @body=@body+' '; set @body=@body+'ГУИД'; set @body=@body+' '; set @body=@body+'ЗАДАНИЕ'; set @body=@body+' '; set @body=@body+'СТАТУС'; set @body=@body+' '; set @body=@body+'ДАТА И ВРЕМЯ'; set @body=@body+' '; set @body=@body+'ДЛИТЕЛЬНОСТЬ'; set @body=@body+' '; set @body=@body+'СООБЩЕНИЕ'; set @body=@body+' '; set @body=@body+'СЕРВЕР'; set @body=@body+'
'; set @body=@body+cast(@ID as nvarchar(max)); set @body=@body+' '; set @body=@body+cast(@Job_GUID as nvarchar(255)); set @body=@body+' '; set @body=@body+coalesce(@Job_Name,''); set @body=@body+' '; set @body=@body+coalesce(@LastFinishRunState,''); set @body=@body+' '; set @body=@body+rep.GetDateFormat(@LastDateTime, default)+' '+rep.GetTimeFormat(@LastDateTime, default);--cast(@InsertDate as nvarchar(max)); set @body=@body+' '; set @body=@body+coalesce(@LastRunDurationString,''); set @body=@body+' '; set @body=@body+coalesce(@LastOutcomeMessage, ''); set @body=@body+' '; set @body=@body+coalesce(@Server, ''); set @body=@body+'
'; end else begin set @body='В ходе анализа последних выполнений заданий, задания с ошибочным завершением, а также те, что выполнились по времени более 30 секунд, не выявлены'; end set @body=@body+'

Для более детальной информации обратитесь к таблице ИМЯ_БАЗЫ_ДАННЫХ.srv.ShortInfoRunJobs'; END GO

Данная хранимая процедура формирует HTML-отчет о выполненных заданиях, которые выполнялись дольше 30 секунд или которые завершились с ошибкой (согласно п.3).

Результат

В статье выше был рассмотрен пример реализации системы ежедневного автоматического сбора информации о выполненных заданиях Агента. С помощью данной информации можно определить задания, которые выполнялись долго по времени или завершились с ошибкой. Это позволяет администратору своевременно принять меры для предотвращения ошибок в дальнейшем.
Например, можно улучшить задание, чтобы оно выполнялось быстрее, или выставить для данного задания максимальное время выше, чем у остальных.
Данное решение также очень помогает для отслеживания проблем с созданием резервных копий (но об этом позже, т. к. один раз в день уведомлять о критически важных ошибках недостаточно, необходимо уведомлять сразу и постоянно повторять уведомление через определенный промежуток времени, пока ошибка не будет исправлена).
Если нужно собирать информацию с нескольких серверов, то можно объединить результат и отправить одним сообщением.

MSSQL — получить список JOB с помощью SQL запроса

Microsoft SQL Server

Пример вывода списка заданий JOB с помощью запроса из системных таблиц. Запрос выводит только те задания, которые созданы в Database Maintenance.

Если вам нужны все, то уберите «AND [sCAT].[name] = N’Database Maintenance'» из запроса.

USE msdb GO SELECT [sJOB].[name] AS [JobName] , [sDBP].[name] AS [JobOwner] , [sCAT].[name] AS [JobCategory] , [sJOB].[description] AS [JobDescription] , [sJSTP].[step_id] AS [JobStartStepNo] , [sJSTP].[step_name] AS [JobStartStepName] , [sJOB].[date_created] AS [JobCreatedOn] , [sJOB].[date_modified] AS [JobLastModifiedOn] , CASE [sJOB].[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes' END AS [IsScheduled] , CASE WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts' WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle' WHEN [freq_type] IN (4, 8, 16, 32) THEN 'Recurring' WHEN [freq_type] = 1 THEN 'One Time' END [ScheduleType] , CASE [freq_type] WHEN 1 THEN 'One Time' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly - Relative to Frequency Interval' WHEN 64 THEN 'Start automatically when SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPUs become idle' END [Occurrence] , CASE [freq_type] WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)' WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on ' + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + ' ' + CASE [freq_interval] WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'Day' WHEN 9 THEN 'Weekday' WHEN 10 THEN 'Weekend day' END + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') + ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') + ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') + ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') END [Frequency] , [sSCH].[name] AS [JobScheduleName] , LastRun = CONVERT(DATETIME, RTRIM(run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),' ','0'),3,0,':'),6,0,':')) , CASE [sJSTP].Last_run_outcome WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 5 THEN 'Unknown' END AS LastRunStatus , LastRunDuration = STUFF(STUFF(REPLACE(STR([sJSTP].last_run_duration,7,0),' ','0'),4,0,':'),7,0,':') , MaxDuration = STUFF(STUFF(REPLACE(STR(l.run_duration,7,0),' ','0'),4,0,':'),7,0,':') , NextRun = CONVERT(DATETIME, RTRIM(NULLIF([sJOBSCH].next_run_date, 0)) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM([sJOBSCH].next_run_time),6,0),' ','0'),3,0,':'),6,0,':')) , CASE [sJOB].[delete_level] WHEN 0 THEN 'Never' WHEN 1 THEN 'On Success' WHEN 2 THEN 'On Failure' WHEN 3 THEN 'On Completion' END AS [JobDeletionCriterion] , [sSVR].[name] AS [OriginatingServerName] , [sJSTP].subsystem AS Subsystem , [sJSTP].command AS Command , h.message AS Message FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id] LEFT JOIN ( SELECT job_id, instance_id = MAX(instance_id), MAX(run_duration) AS run_duration FROM msdb.dbo.sysjobhistory GROUP BY job_id ) AS l ON sJOB.job_id = l.job_id LEFT JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = l.job_id AND h.instance_id = l.instance_id ORDER BY [JobName]

[JobName]: название задания агента SQL Server.
[JobOwner]: владелец задания.
[JobCategory]: категория, к которой относится задание, например моментальный снимок репликации, обслуживание базы данных, отправка журналов и т.д.
[JobDescription]: описание задания.
[JobStartStepNo]: номер шага, из которого задано задание для запуска.
[JobStartStepName]: имя шага, с которого задание начинается.
[JobCreatedOn]: дата и время создания задания.
[JobLastModifiedOn]: дата и время последнего изменения задания.
[IsEnabled]: индикатор, показывающий, включено ли задание.
[IsScheduled]: индикатор, указывающий, запланировано ли задание или нет.
[ScheduleType]: тип расписания.
[Occurrence]: график задания, такого как Ежедневный, Еженедельный, Ежемесячный и т.д.
[Recurrence]: повторение графика.
[Frequency]: как часто задание должно выполняться.
[JobScheduleName]: имя расписания, связанного с заданием.
[LastRun]: дата и время выполнения задания в последний раз (соответствует самому последнему запуску).
[LastRunStatus]: состояние или результат последнего запуска задания.
[LastRunDuration]: Продолжительность последнего выполнения задания.
[MaxDuration]: максимальная продолжительность задания.
[NextRun]: дата и время следующего запуска.
[JobDeletionCriterion]: критерий для удаления задания.
[OriginatingServerName]: сервер, с которого выполнялось задание.
[Subsystem]: тип операции, например, интеграция с SQL Server Пакет услуг, Transact-SQL Script (T-SQL), ActiveX Script и т.д.
[Command]: фактическая команда, которая будет выполнена.
[Message]: информация о успехе/неудаче работы и т.д.

WTFM.INFO

Write The F* Manual — Заметки о сетях, администрировании и вообще

MS SQL определение Job-ы от которой запущена транзакция

Если в выводе процедуры sp_WhoIsActive (http://whoisactive.com/) искомая транзакция (например, транзакция которая долго выполняется или вешает другие транзакции) в поле program_name содержит запись вида:

SQLAgent - TSQL JobStep (Job 0x3E7DB8B391F4CB4AA406DDF1A3644E5B : Step 1)

Значит эта транзакция запущена Job-ой. Чтобы определить что это за Job-а, необходимо подключиться студией SSMS к инстансу сервера с которого запущена Job-а — в выводе sp_WhoIsActive сервер указан в поле host_name, затем выполнить на нем запрос:

SELECT * FROM msdb.dbo.sysjobs WHERE CONVERT(binary(16), job_id)=0xC50B629D34BB244589E226D81312BECF

Подставив после знака «=» значение из записи в поле program_name.

В результате выполнения запроса в поле name будет содержаться название искомой Job-ы из SQL Server Agent. Номер шага указан в выводе процедуры sp_WhoIsActive после ID Job-ы (Step).