T-SQL: Поиск по джобам

Для поиска джоба можно применять следующий скрипт:

select j.job_id, j.name, s.step_name, j.description
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id
where s.command like '%some_text%'

s.command - команда, которую выполняет шаг джоба;
j.name - название джоба;
s.step_name - название шага джоба;
j.description - описание джоба.

T-SQL: Как проверить выполнение джоба

Бывают случаи, когда необходимо сделать так, что в джобе некоторые шаги при ошибочном выполнении не приводят к завершению джоба с ошибкой, а переходят на другой шаг джоба. У меня таким образом работает куб, который процессит OLAP-кубы. Что бы не следить каждый день за выполнением джоба, мы настроили SMS-рассылку. Для этого потребовалось разработать механизм проверки работы джоба. Т.е. задача - найти шаги джоба, которые завершились ошибкой, но при этом джоб продолжил работу.

Для начала нужно получить ID джоба:

select job_id from msdb..sysjobs where name = @job_name

Лог работы джобов лежит в таблице msdb..sysjobhistory. Нужно вытащить записи, которые относятся к последнему выполнению джоба. У меня джоб выполняется каждый день, поэтому просто беру сегодняшнее число. Но в таблице msdb..sysjobhistory дата начала выполнения шага храниться не в datetime, а в двух полях типа int: run_date и run_time, т.е. дата 2011-08-11 13:54:33 будет лежать в виде run_date = 20110811 и run_time = 135433.

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

 -- Конвертация времени из формата HHMMSS типа int в секунды
create function [dbo].[fn_convert_intdt_to_sec] ( @ts int )
    returns int
as
begin
    if (@ts < 60) return @ts
     
    declare @sec int = @ts - (@ts/100)*100
    declare @min int = (@ts - (@ts/10000)*10000)/100
    declare @hr int = @ts/10000
     
    return @hr*3600 + @min*60 + @sec
end

-- Конвертация времени из формата даты в джобе в нормальный DateTime
create function [dbo].[fn_convert_job_date_to_datetime]( @date int, @time int )
returns DateTime
as
begin
    return dateadd(second, dbo.fn_convert_intdt_to_sec(@time), convert(datetime, cast(@date as varchar), 112))
end

Далее выводим список шагов:

select instance_id, step_id, step_name, run_status, dbo.fn_convert_job_date_to_datetime(run_date, run_time) run_dt
from msdb..sysjobhistory
where job_id = @job_id
and step_id <> 0 and dbo.fn_convert_job_date_to_datetime(run_date, run_time) >= @dt
order by step_id

step_id = 0 - это строка для всего джоба, поэтомы ее исключаем.

Тут (http://msdn.microsoft.com/en-us/library/ms174997.aspx) лежит описание таблицы  msdb..sysjobhistory.Статусы у шага могут быть такие:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled

 В проге, которая дергает данные, я проверяю, что у всех шагов статус = 1 , если нет, то шлю СМС с ошибкой.

Как запускать зашифрованный SSIS пакет из джоба

При создании SSIS пакета в BIDS в свойствах есть опция ProtectionLevel со следующими возможными значениями:

1. DontSaveSensitive - не сохранять в пакете паролей (имеются в виду пароли от соединений к БД и другим источникам и получателям данных);
2. EncryptSensitiveWithUserKey - зашифровывать пароли с помощью ключа пользователя, который основан на профайле пользователя)
3. EncryptSensitiveWithPassword - зашифровывать пароли с помощью пароля, который вводится в свойствах в поле PackagePassword;
4. EncryptAllWithPassword - шифровать все введенным паролем;
5. EncryptAllWithUserKey - шифровать все с помощью ключа пользователя;
6. ServerStorage - сохранить пакет в базе msdb и защитить его с помощью ролей базы данных.

По умолчанию стоит EncryptSensitveWithUserKey.

Подробнее можно почитать тут: http://msdn.microsoft.com/en-us/library/ms141747.aspx

Последний пункт ни когда не использовал, т.к. предпочитаю запускать пакеты прямо из файлов, мне так удобнее. Но видимо, если пойти путем сохранения пакета в БД, то проблема пароля снимается.

А проблема состоит в том, что при использовании UserKey для шифрования SQL Server Agent должен быть запущен из под того же пользователя, что и делал сам пакет. Когда на сервере работает один пользователь проблем с этим не возникает. 

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

На новом проекте пакеты делаются одним пользователем, а SQL Server Agent запущен из под другого и поменять это никак нельзя. Поэтому я стал использовать опцию EncryptSensitiveWithPassword. 

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

1. В редакторе шага джоба выбрать type = SQL Server Integration Services Package;
2. Package source = File system;
3. Package - указать путь к файлу;
4. На вкладке Command line (при ее открытии редактор попросит ввести пароль, который был использован для шифрования в пакете) выбрать Edit the command line manually. Для редактирования откроется поле Command line и там после опции /DECRYPT надо ввести пароль, т.е. /DECRYPT password.
5. Нажать OK для сохранения шага.

Подробнее об опциях командной строки при запуске SSIS-пакетов можно почитать тут: http://msdn.microsoft.com/en-us/library/ms162810.aspx .

Работа с логами джобов в SQL Server

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

CREATE TABLE dbo.sysjobhistory(
    instance_id int IDENTITY(1,1) NOT NULL,
    job_id uniqueidentifier NOT NULL,
    step_id int NOT NULL,
    step_name sysname NOT NULL,
    sql_message_id int NOT NULL,
    sql_severity int NOT NULL,
    message nvarchar(4000) NULL,
    run_status int NOT NULL,
    run_date int NOT NULL,
    run_time int NOT NULL,
    run_duration int NOT NULL,
    operator_id_emailed int NOT NULL,
    operator_id_netsent int NOT NULL,
    operator_id_paged int NOT NULL,
    retries_attempted int NOT NULL,
    server sysname NOT NULL
)

Тут есть описание этой таблицы.
job_id определяется по таблице  msdb..sysjobs, например вот так:

SELECT * FROM msdb..sysjobs WHERE name like '%job%'

Детально со всеми полями не разбирался, назначение большинства понятно из названий. Меня интересовало поле run_duration. Поле имеет тип int, там хранится время выполнения шага (строки, в которых step_id = 0, относятся ко всему джобу, т.е. в такой строке в поле run_duration будет время выполнения всего джоба). Формат: hhmmss, поскольку тип int, нули слева не отображаются. Т.е. значение 709 - это 7 минут 9 сек, 34508 - 3 часа 45 минут 8 секунд.
Для преобразования этого формата в секунды я написал функцию:

-- Конвертация времени из формата HHMMSS типа int в секунды

create function fn_convert_intdt_to_sec ( @ts int )
returns int
as
begin
    if (@ts < 60) return @ts
   
    declare @sec int = @ts - (@ts/100)*100
    declare @min int = (@ts - (@ts/10000)*10000)/100
    declare @hr int = @ts/10000
   
    return @hr*3600 + @min*60 + @sec

end

В принципе если в передаваемом значении часы будут содержать более 2-х знаков, то тоже отработает.