Задание.
Не обходим аудит который отслеживал кто изменил , создал, удалил, задание или базу, а также кто менял настройки mssql.
Был выбран Profiler так как в нем уже есть шаблоны аудита.
Для на чала создаем трассировку в самом Profiler.
Потом делаем определение трассировки
Получаем вот такой код
-- ====================================================================================================== -- Author: Константин Москвичёв -- Create date: 13/04/2015 -- Description: 0.2b -- Описание: Запускает трасировку для аудита. -- ====================================================================================================== declare @rc int declare @TraceID int declare @maxfilesize bigint declare @filename nvarchar(128) declare @filecount int --указываем максимальный размер файла set @maxfilesize = 99 --указываем путь и имя файла, к имени файла дописывается текушия дата и время set @filename = '\\192.168.0.38\Backup\audit_rx6600\TracE'+CONVERT(CHAR(20),GETDATE(),112)+REPLACE(CONVERT(CHAR(20),GETDATE(),108),':','') -- НАстройка параметров трасировки exec @rc = sp_trace_create @traceid = @TraceID output, @options = 2, --опция 2 https://msdn.microsoft.com/ru-ru/library/ms190362.aspx @tracefile = @filename, --имя трасировки @maxfilesize = @maxfilesize, -- максимальный размер трасировки @stoptime = NULL, -- не ограниченое время выполнения @filecount = 50 -- не больше 50 файлов хранить if (@rc != 0) goto error -- Сюда можно добавить любые параметры трасировки из sql Profile. -- В данный момент здась применен шаблон audit с фильтром -- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| -- \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 109, 7, @on exec sp_trace_setevent @TraceID, 109, 23, @on exec sp_trace_setevent @TraceID, 109, 39, @on exec sp_trace_setevent @TraceID, 109, 8, @on exec sp_trace_setevent @TraceID, 109, 40, @on exec sp_trace_setevent @TraceID, 109, 64, @on exec sp_trace_setevent @TraceID, 109, 9, @on exec sp_trace_setevent @TraceID, 109, 41, @on exec sp_trace_setevent @TraceID, 109, 49, @on exec sp_trace_setevent @TraceID, 109, 10, @on exec sp_trace_setevent @TraceID, 109, 26, @on exec sp_trace_setevent @TraceID, 109, 42, @on exec sp_trace_setevent @TraceID, 109, 50, @on exec sp_trace_setevent @TraceID, 109, 3, @on exec sp_trace_setevent @TraceID, 109, 11, @on exec sp_trace_setevent @TraceID, 109, 35, @on exec sp_trace_setevent @TraceID, 109, 43, @on exec sp_trace_setevent @TraceID, 109, 51, @on exec sp_trace_setevent @TraceID, 109, 4, @on exec sp_trace_setevent @TraceID, 109, 12, @on exec sp_trace_setevent @TraceID, 109, 28, @on exec sp_trace_setevent @TraceID, 109, 44, @on exec sp_trace_setevent @TraceID, 109, 60, @on exec sp_trace_setevent @TraceID, 109, 21, @on exec sp_trace_setevent @TraceID, 109, 29, @on exec sp_trace_setevent @TraceID, 109, 37, @on exec sp_trace_setevent @TraceID, 109, 6, @on exec sp_trace_setevent @TraceID, 109, 14, @on exec sp_trace_setevent @TraceID, 109, 38, @on exec sp_trace_setevent @TraceID, 111, 7, @on exec sp_trace_setevent @TraceID, 111, 23, @on exec sp_trace_setevent @TraceID, 111, 8, @on exec sp_trace_setevent @TraceID, 111, 40, @on exec sp_trace_setevent @TraceID, 111, 64, @on exec sp_trace_setevent @TraceID, 111, 9, @on exec sp_trace_setevent @TraceID, 111, 41, @on exec sp_trace_setevent @TraceID, 111, 49, @on exec sp_trace_setevent @TraceID, 111, 6, @on exec sp_trace_setevent @TraceID, 111, 10, @on exec sp_trace_setevent @TraceID, 111, 14, @on exec sp_trace_setevent @TraceID, 111, 26, @on exec sp_trace_setevent @TraceID, 111, 38, @on exec sp_trace_setevent @TraceID, 111, 50, @on exec sp_trace_setevent @TraceID, 111, 3, @on exec sp_trace_setevent @TraceID, 111, 11, @on exec sp_trace_setevent @TraceID, 111, 35, @on exec sp_trace_setevent @TraceID, 111, 51, @on exec sp_trace_setevent @TraceID, 111, 4, @on exec sp_trace_setevent @TraceID, 111, 12, @on exec sp_trace_setevent @TraceID, 111, 28, @on exec sp_trace_setevent @TraceID, 111, 60, @on exec sp_trace_setevent @TraceID, 111, 21, @on exec sp_trace_setevent @TraceID, 111, 29, @on exec sp_trace_setevent @TraceID, 104, 7, @on exec sp_trace_setevent @TraceID, 104, 23, @on exec sp_trace_setevent @TraceID, 104, 8, @on exec sp_trace_setevent @TraceID, 104, 40, @on exec sp_trace_setevent @TraceID, 104, 64, @on exec sp_trace_setevent @TraceID, 104, 9, @on exec sp_trace_setevent @TraceID, 104, 41, @on exec sp_trace_setevent @TraceID, 104, 49, @on exec sp_trace_setevent @TraceID, 104, 6, @on exec sp_trace_setevent @TraceID, 104, 10, @on exec sp_trace_setevent @TraceID, 104, 14, @on exec sp_trace_setevent @TraceID, 104, 26, @on exec sp_trace_setevent @TraceID, 104, 42, @on exec sp_trace_setevent @TraceID, 104, 50, @on exec sp_trace_setevent @TraceID, 104, 3, @on exec sp_trace_setevent @TraceID, 104, 11, @on exec sp_trace_setevent @TraceID, 104, 35, @on exec sp_trace_setevent @TraceID, 104, 43, @on exec sp_trace_setevent @TraceID, 104, 51, @on exec sp_trace_setevent @TraceID, 104, 4, @on exec sp_trace_setevent @TraceID, 104, 12, @on exec sp_trace_setevent @TraceID, 104, 28, @on exec sp_trace_setevent @TraceID, 104, 60, @on exec sp_trace_setevent @TraceID, 104, 5, @on exec sp_trace_setevent @TraceID, 104, 21, @on exec sp_trace_setevent @TraceID, 128, 7, @on exec sp_trace_setevent @TraceID, 128, 23, @on exec sp_trace_setevent @TraceID, 128, 8, @on exec sp_trace_setevent @TraceID, 128, 40, @on exec sp_trace_setevent @TraceID, 128, 64, @on exec sp_trace_setevent @TraceID, 128, 1, @on exec sp_trace_setevent @TraceID, 128, 41, @on exec sp_trace_setevent @TraceID, 128, 49, @on exec sp_trace_setevent @TraceID, 128, 6, @on exec sp_trace_setevent @TraceID, 128, 10, @on exec sp_trace_setevent @TraceID, 128, 14, @on exec sp_trace_setevent @TraceID, 128, 26, @on exec sp_trace_setevent @TraceID, 128, 34, @on exec sp_trace_setevent @TraceID, 128, 50, @on exec sp_trace_setevent @TraceID, 128, 3, @on exec sp_trace_setevent @TraceID, 128, 11, @on exec sp_trace_setevent @TraceID, 128, 35, @on exec sp_trace_setevent @TraceID, 128, 51, @on exec sp_trace_setevent @TraceID, 128, 4, @on exec sp_trace_setevent @TraceID, 128, 12, @on exec sp_trace_setevent @TraceID, 128, 28, @on exec sp_trace_setevent @TraceID, 128, 60, @on exec sp_trace_setevent @TraceID, 128, 21, @on exec sp_trace_setevent @TraceID, 128, 29, @on exec sp_trace_setevent @TraceID, 128, 37, @on exec sp_trace_setevent @TraceID, 130, 7, @on exec sp_trace_setevent @TraceID, 130, 23, @on exec sp_trace_setevent @TraceID, 130, 39, @on exec sp_trace_setevent @TraceID, 130, 8, @on exec sp_trace_setevent @TraceID, 130, 40, @on exec sp_trace_setevent @TraceID, 130, 64, @on exec sp_trace_setevent @TraceID, 130, 1, @on exec sp_trace_setevent @TraceID, 130, 41, @on exec sp_trace_setevent @TraceID, 130, 49, @on exec sp_trace_setevent @TraceID, 130, 6, @on exec sp_trace_setevent @TraceID, 130, 10, @on exec sp_trace_setevent @TraceID, 130, 14, @on exec sp_trace_setevent @TraceID, 130, 26, @on exec sp_trace_setevent @TraceID, 130, 34, @on exec sp_trace_setevent @TraceID, 130, 42, @on exec sp_trace_setevent @TraceID, 130, 50, @on exec sp_trace_setevent @TraceID, 130, 3, @on exec sp_trace_setevent @TraceID, 130, 11, @on exec sp_trace_setevent @TraceID, 130, 35, @on exec sp_trace_setevent @TraceID, 130, 43, @on exec sp_trace_setevent @TraceID, 130, 51, @on exec sp_trace_setevent @TraceID, 130, 4, @on exec sp_trace_setevent @TraceID, 130, 12, @on exec sp_trace_setevent @TraceID, 130, 28, @on exec sp_trace_setevent @TraceID, 130, 60, @on exec sp_trace_setevent @TraceID, 130, 21, @on exec sp_trace_setevent @TraceID, 130, 29, @on exec sp_trace_setevent @TraceID, 130, 37, @on exec sp_trace_setevent @TraceID, 107, 7, @on exec sp_trace_setevent @TraceID, 107, 23, @on exec sp_trace_setevent @TraceID, 107, 8, @on exec sp_trace_setevent @TraceID, 107, 40, @on exec sp_trace_setevent @TraceID, 107, 64, @on exec sp_trace_setevent @TraceID, 107, 1, @on exec sp_trace_setevent @TraceID, 107, 9, @on exec sp_trace_setevent @TraceID, 107, 41, @on exec sp_trace_setevent @TraceID, 107, 49, @on exec sp_trace_setevent @TraceID, 107, 10, @on exec sp_trace_setevent @TraceID, 107, 26, @on exec sp_trace_setevent @TraceID, 107, 34, @on exec sp_trace_setevent @TraceID, 107, 42, @on exec sp_trace_setevent @TraceID, 107, 50, @on exec sp_trace_setevent @TraceID, 107, 3, @on exec sp_trace_setevent @TraceID, 107, 11, @on exec sp_trace_setevent @TraceID, 107, 35, @on exec sp_trace_setevent @TraceID, 107, 43, @on exec sp_trace_setevent @TraceID, 107, 51, @on exec sp_trace_setevent @TraceID, 107, 4, @on exec sp_trace_setevent @TraceID, 107, 12, @on exec sp_trace_setevent @TraceID, 107, 28, @on exec sp_trace_setevent @TraceID, 107, 60, @on exec sp_trace_setevent @TraceID, 107, 21, @on exec sp_trace_setevent @TraceID, 107, 29, @on exec sp_trace_setevent @TraceID, 107, 37, @on exec sp_trace_setevent @TraceID, 107, 6, @on exec sp_trace_setevent @TraceID, 107, 14, @on exec sp_trace_setevent @TraceID, 118, 7, @on exec sp_trace_setevent @TraceID, 118, 23, @on exec sp_trace_setevent @TraceID, 118, 8, @on exec sp_trace_setevent @TraceID, 118, 40, @on exec sp_trace_setevent @TraceID, 118, 64, @on exec sp_trace_setevent @TraceID, 118, 1, @on exec sp_trace_setevent @TraceID, 118, 9, @on exec sp_trace_setevent @TraceID, 118, 41, @on exec sp_trace_setevent @TraceID, 118, 49, @on exec sp_trace_setevent @TraceID, 118, 6, @on exec sp_trace_setevent @TraceID, 118, 10, @on exec sp_trace_setevent @TraceID, 118, 14, @on exec sp_trace_setevent @TraceID, 118, 26, @on exec sp_trace_setevent @TraceID, 118, 34, @on exec sp_trace_setevent @TraceID, 118, 50, @on exec sp_trace_setevent @TraceID, 118, 3, @on exec sp_trace_setevent @TraceID, 118, 11, @on exec sp_trace_setevent @TraceID, 118, 35, @on exec sp_trace_setevent @TraceID, 118, 51, @on exec sp_trace_setevent @TraceID, 118, 4, @on exec sp_trace_setevent @TraceID, 118, 12, @on exec sp_trace_setevent @TraceID, 118, 28, @on exec sp_trace_setevent @TraceID, 118, 60, @on exec sp_trace_setevent @TraceID, 118, 5, @on exec sp_trace_setevent @TraceID, 118, 21, @on exec sp_trace_setevent @TraceID, 118, 29, @on exec sp_trace_setevent @TraceID, 118, 37, @on exec sp_trace_setevent @TraceID, 114, 7, @on exec sp_trace_setevent @TraceID, 114, 23, @on exec sp_trace_setevent @TraceID, 114, 8, @on exec sp_trace_setevent @TraceID, 114, 40, @on exec sp_trace_setevent @TraceID, 114, 64, @on exec sp_trace_setevent @TraceID, 114, 1, @on exec sp_trace_setevent @TraceID, 114, 9, @on exec sp_trace_setevent @TraceID, 114, 41, @on exec sp_trace_setevent @TraceID, 114, 49, @on exec sp_trace_setevent @TraceID, 114, 2, @on exec sp_trace_setevent @TraceID, 114, 10, @on exec sp_trace_setevent @TraceID, 114, 26, @on exec sp_trace_setevent @TraceID, 114, 34, @on exec sp_trace_setevent @TraceID, 114, 50, @on exec sp_trace_setevent @TraceID, 114, 3, @on exec sp_trace_setevent @TraceID, 114, 11, @on exec sp_trace_setevent @TraceID, 114, 19, @on exec sp_trace_setevent @TraceID, 114, 35, @on exec sp_trace_setevent @TraceID, 114, 51, @on exec sp_trace_setevent @TraceID, 114, 59, @on exec sp_trace_setevent @TraceID, 114, 4, @on exec sp_trace_setevent @TraceID, 114, 12, @on exec sp_trace_setevent @TraceID, 114, 28, @on exec sp_trace_setevent @TraceID, 114, 44, @on exec sp_trace_setevent @TraceID, 114, 60, @on exec sp_trace_setevent @TraceID, 114, 5, @on exec sp_trace_setevent @TraceID, 114, 21, @on exec sp_trace_setevent @TraceID, 114, 29, @on exec sp_trace_setevent @TraceID, 114, 37, @on exec sp_trace_setevent @TraceID, 114, 6, @on exec sp_trace_setevent @TraceID, 114, 14, @on exec sp_trace_setevent @TraceID, 176, 7, @on exec sp_trace_setevent @TraceID, 176, 23, @on exec sp_trace_setevent @TraceID, 176, 8, @on exec sp_trace_setevent @TraceID, 176, 40, @on exec sp_trace_setevent @TraceID, 176, 64, @on exec sp_trace_setevent @TraceID, 176, 1, @on exec sp_trace_setevent @TraceID, 176, 9, @on exec sp_trace_setevent @TraceID, 176, 41, @on exec sp_trace_setevent @TraceID, 176, 49, @on exec sp_trace_setevent @TraceID, 176, 10, @on exec sp_trace_setevent @TraceID, 176, 26, @on exec sp_trace_setevent @TraceID, 176, 34, @on exec sp_trace_setevent @TraceID, 176, 50, @on exec sp_trace_setevent @TraceID, 176, 3, @on exec sp_trace_setevent @TraceID, 176, 11, @on exec sp_trace_setevent @TraceID, 176, 35, @on exec sp_trace_setevent @TraceID, 176, 51, @on exec sp_trace_setevent @TraceID, 176, 4, @on exec sp_trace_setevent @TraceID, 176, 12, @on exec sp_trace_setevent @TraceID, 176, 28, @on exec sp_trace_setevent @TraceID, 176, 60, @on exec sp_trace_setevent @TraceID, 176, 21, @on exec sp_trace_setevent @TraceID, 176, 29, @on exec sp_trace_setevent @TraceID, 176, 37, @on exec sp_trace_setevent @TraceID, 176, 45, @on exec sp_trace_setevent @TraceID, 176, 6, @on exec sp_trace_setevent @TraceID, 176, 14, @on exec sp_trace_setevent @TraceID, 176, 46, @on exec sp_trace_setevent @TraceID, 134, 7, @on exec sp_trace_setevent @TraceID, 134, 23, @on exec sp_trace_setevent @TraceID, 134, 39, @on exec sp_trace_setevent @TraceID, 134, 8, @on exec sp_trace_setevent @TraceID, 134, 40, @on exec sp_trace_setevent @TraceID, 134, 64, @on exec sp_trace_setevent @TraceID, 134, 1, @on exec sp_trace_setevent @TraceID, 134, 41, @on exec sp_trace_setevent @TraceID, 134, 49, @on exec sp_trace_setevent @TraceID, 134, 6, @on exec sp_trace_setevent @TraceID, 134, 10, @on exec sp_trace_setevent @TraceID, 134, 14, @on exec sp_trace_setevent @TraceID, 134, 26, @on exec sp_trace_setevent @TraceID, 134, 34, @on exec sp_trace_setevent @TraceID, 134, 42, @on exec sp_trace_setevent @TraceID, 134, 50, @on exec sp_trace_setevent @TraceID, 134, 3, @on exec sp_trace_setevent @TraceID, 134, 11, @on exec sp_trace_setevent @TraceID, 134, 35, @on exec sp_trace_setevent @TraceID, 134, 43, @on exec sp_trace_setevent @TraceID, 134, 51, @on exec sp_trace_setevent @TraceID, 134, 4, @on exec sp_trace_setevent @TraceID, 134, 12, @on exec sp_trace_setevent @TraceID, 134, 28, @on exec sp_trace_setevent @TraceID, 134, 60, @on exec sp_trace_setevent @TraceID, 134, 29, @on exec sp_trace_setevent @TraceID, 134, 37, @on exec sp_trace_setevent @TraceID, 170, 7, @on exec sp_trace_setevent @TraceID, 170, 23, @on exec sp_trace_setevent @TraceID, 170, 8, @on exec sp_trace_setevent @TraceID, 170, 40, @on exec sp_trace_setevent @TraceID, 170, 64, @on exec sp_trace_setevent @TraceID, 170, 1, @on exec sp_trace_setevent @TraceID, 170, 9, @on exec sp_trace_setevent @TraceID, 170, 41, @on exec sp_trace_setevent @TraceID, 170, 49, @on exec sp_trace_setevent @TraceID, 170, 10, @on exec sp_trace_setevent @TraceID, 170, 26, @on exec sp_trace_setevent @TraceID, 170, 34, @on exec sp_trace_setevent @TraceID, 170, 42, @on exec sp_trace_setevent @TraceID, 170, 50, @on exec sp_trace_setevent @TraceID, 170, 3, @on exec sp_trace_setevent @TraceID, 170, 11, @on exec sp_trace_setevent @TraceID, 170, 19, @on exec sp_trace_setevent @TraceID, 170, 35, @on exec sp_trace_setevent @TraceID, 170, 43, @on exec sp_trace_setevent @TraceID, 170, 51, @on exec sp_trace_setevent @TraceID, 170, 4, @on exec sp_trace_setevent @TraceID, 170, 12, @on exec sp_trace_setevent @TraceID, 170, 28, @on exec sp_trace_setevent @TraceID, 170, 60, @on exec sp_trace_setevent @TraceID, 170, 21, @on exec sp_trace_setevent @TraceID, 170, 29, @on exec sp_trace_setevent @TraceID, 170, 37, @on exec sp_trace_setevent @TraceID, 170, 6, @on exec sp_trace_setevent @TraceID, 170, 14, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 1, 0, 7, N'select' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Приложение SQL Server Profiler - 342699b4-dba3-411d-9901-e3ba875c146a' exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_dbcmptlevel' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_update_job' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_add_job' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_delete_job' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
После этого берем код и вставляем в мой шаблон
-- ====================================================================================================== -- Author: Константин Москвичёв -- Create date: 13/04/2015 -- Description: 0.2b -- Описание: Запускает трасировку для аудита. -- ====================================================================================================== declare @rc int declare @TraceID int declare @maxfilesize bigint declare @filename nvarchar(128) declare @filecount int --указываем максимальный размер файла set @maxfilesize = 1000 --указываем путь и имя файла, к имени файла дописывается текушия дата и время set @filename = '\\192.168.0.38\Backup\audit_rx6600\TracE'+CONVERT(CHAR(20),GETDATE(),112)+REPLACE(CONVERT(CHAR(20),GETDATE(),108),':','') -- НАстройка параметров трасировки exec @rc = sp_trace_create @traceid = @TraceID output, @options = 2, --опция 2 https://msdn.microsoft.com/ru-ru/library/ms190362.aspx @tracefile = @filename, --имя трасировки @maxfilesize = @maxfilesize, -- максимальный размер трасировки @stoptime = NULL, -- не ограниченое время выполнения @filecount = 10 -- не больше 50 файлов хранить if (@rc != 0) goto error -- Сюда можно добавить любые параметры трасировки из sql Profile. -- В данный момент задать применен шаблон audit с фильтром -- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| -- \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 109, 7, @on exec sp_trace_setevent @TraceID, 109, 23, @on exec sp_trace_setevent @TraceID, 109, 39, @on exec sp_trace_setevent @TraceID, 109, 8, @on exec sp_trace_setevent @TraceID, 109, 40, @on exec sp_trace_setevent @TraceID, 109, 64, @on exec sp_trace_setevent @TraceID, 109, 9, @on exec sp_trace_setevent @TraceID, 109, 41, @on exec sp_trace_setevent @TraceID, 109, 49, @on exec sp_trace_setevent @TraceID, 109, 10, @on exec sp_trace_setevent @TraceID, 109, 26, @on exec sp_trace_setevent @TraceID, 109, 42, @on exec sp_trace_setevent @TraceID, 109, 50, @on exec sp_trace_setevent @TraceID, 109, 3, @on exec sp_trace_setevent @TraceID, 109, 11, @on exec sp_trace_setevent @TraceID, 109, 35, @on exec sp_trace_setevent @TraceID, 109, 43, @on exec sp_trace_setevent @TraceID, 109, 51, @on exec sp_trace_setevent @TraceID, 109, 4, @on exec sp_trace_setevent @TraceID, 109, 12, @on exec sp_trace_setevent @TraceID, 109, 28, @on exec sp_trace_setevent @TraceID, 109, 44, @on exec sp_trace_setevent @TraceID, 109, 60, @on exec sp_trace_setevent @TraceID, 109, 21, @on exec sp_trace_setevent @TraceID, 109, 29, @on exec sp_trace_setevent @TraceID, 109, 37, @on exec sp_trace_setevent @TraceID, 109, 6, @on exec sp_trace_setevent @TraceID, 109, 14, @on exec sp_trace_setevent @TraceID, 109, 38, @on exec sp_trace_setevent @TraceID, 111, 7, @on exec sp_trace_setevent @TraceID, 111, 23, @on exec sp_trace_setevent @TraceID, 111, 8, @on exec sp_trace_setevent @TraceID, 111, 40, @on exec sp_trace_setevent @TraceID, 111, 64, @on exec sp_trace_setevent @TraceID, 111, 9, @on exec sp_trace_setevent @TraceID, 111, 41, @on exec sp_trace_setevent @TraceID, 111, 49, @on exec sp_trace_setevent @TraceID, 111, 6, @on exec sp_trace_setevent @TraceID, 111, 10, @on exec sp_trace_setevent @TraceID, 111, 14, @on exec sp_trace_setevent @TraceID, 111, 26, @on exec sp_trace_setevent @TraceID, 111, 38, @on exec sp_trace_setevent @TraceID, 111, 50, @on exec sp_trace_setevent @TraceID, 111, 3, @on exec sp_trace_setevent @TraceID, 111, 11, @on exec sp_trace_setevent @TraceID, 111, 35, @on exec sp_trace_setevent @TraceID, 111, 51, @on exec sp_trace_setevent @TraceID, 111, 4, @on exec sp_trace_setevent @TraceID, 111, 12, @on exec sp_trace_setevent @TraceID, 111, 28, @on exec sp_trace_setevent @TraceID, 111, 60, @on exec sp_trace_setevent @TraceID, 111, 21, @on exec sp_trace_setevent @TraceID, 111, 29, @on exec sp_trace_setevent @TraceID, 104, 7, @on exec sp_trace_setevent @TraceID, 104, 23, @on exec sp_trace_setevent @TraceID, 104, 8, @on exec sp_trace_setevent @TraceID, 104, 40, @on exec sp_trace_setevent @TraceID, 104, 64, @on exec sp_trace_setevent @TraceID, 104, 9, @on exec sp_trace_setevent @TraceID, 104, 41, @on exec sp_trace_setevent @TraceID, 104, 49, @on exec sp_trace_setevent @TraceID, 104, 6, @on exec sp_trace_setevent @TraceID, 104, 10, @on exec sp_trace_setevent @TraceID, 104, 14, @on exec sp_trace_setevent @TraceID, 104, 26, @on exec sp_trace_setevent @TraceID, 104, 42, @on exec sp_trace_setevent @TraceID, 104, 50, @on exec sp_trace_setevent @TraceID, 104, 3, @on exec sp_trace_setevent @TraceID, 104, 11, @on exec sp_trace_setevent @TraceID, 104, 35, @on exec sp_trace_setevent @TraceID, 104, 43, @on exec sp_trace_setevent @TraceID, 104, 51, @on exec sp_trace_setevent @TraceID, 104, 4, @on exec sp_trace_setevent @TraceID, 104, 12, @on exec sp_trace_setevent @TraceID, 104, 28, @on exec sp_trace_setevent @TraceID, 104, 60, @on exec sp_trace_setevent @TraceID, 104, 5, @on exec sp_trace_setevent @TraceID, 104, 21, @on exec sp_trace_setevent @TraceID, 128, 7, @on exec sp_trace_setevent @TraceID, 128, 23, @on exec sp_trace_setevent @TraceID, 128, 8, @on exec sp_trace_setevent @TraceID, 128, 40, @on exec sp_trace_setevent @TraceID, 128, 64, @on exec sp_trace_setevent @TraceID, 128, 1, @on exec sp_trace_setevent @TraceID, 128, 41, @on exec sp_trace_setevent @TraceID, 128, 49, @on exec sp_trace_setevent @TraceID, 128, 6, @on exec sp_trace_setevent @TraceID, 128, 10, @on exec sp_trace_setevent @TraceID, 128, 14, @on exec sp_trace_setevent @TraceID, 128, 26, @on exec sp_trace_setevent @TraceID, 128, 34, @on exec sp_trace_setevent @TraceID, 128, 50, @on exec sp_trace_setevent @TraceID, 128, 3, @on exec sp_trace_setevent @TraceID, 128, 11, @on exec sp_trace_setevent @TraceID, 128, 35, @on exec sp_trace_setevent @TraceID, 128, 51, @on exec sp_trace_setevent @TraceID, 128, 4, @on exec sp_trace_setevent @TraceID, 128, 12, @on exec sp_trace_setevent @TraceID, 128, 28, @on exec sp_trace_setevent @TraceID, 128, 60, @on exec sp_trace_setevent @TraceID, 128, 21, @on exec sp_trace_setevent @TraceID, 128, 29, @on exec sp_trace_setevent @TraceID, 128, 37, @on exec sp_trace_setevent @TraceID, 130, 7, @on exec sp_trace_setevent @TraceID, 130, 23, @on exec sp_trace_setevent @TraceID, 130, 39, @on exec sp_trace_setevent @TraceID, 130, 8, @on exec sp_trace_setevent @TraceID, 130, 40, @on exec sp_trace_setevent @TraceID, 130, 64, @on exec sp_trace_setevent @TraceID, 130, 1, @on exec sp_trace_setevent @TraceID, 130, 41, @on exec sp_trace_setevent @TraceID, 130, 49, @on exec sp_trace_setevent @TraceID, 130, 6, @on exec sp_trace_setevent @TraceID, 130, 10, @on exec sp_trace_setevent @TraceID, 130, 14, @on exec sp_trace_setevent @TraceID, 130, 26, @on exec sp_trace_setevent @TraceID, 130, 34, @on exec sp_trace_setevent @TraceID, 130, 42, @on exec sp_trace_setevent @TraceID, 130, 50, @on exec sp_trace_setevent @TraceID, 130, 3, @on exec sp_trace_setevent @TraceID, 130, 11, @on exec sp_trace_setevent @TraceID, 130, 35, @on exec sp_trace_setevent @TraceID, 130, 43, @on exec sp_trace_setevent @TraceID, 130, 51, @on exec sp_trace_setevent @TraceID, 130, 4, @on exec sp_trace_setevent @TraceID, 130, 12, @on exec sp_trace_setevent @TraceID, 130, 28, @on exec sp_trace_setevent @TraceID, 130, 60, @on exec sp_trace_setevent @TraceID, 130, 21, @on exec sp_trace_setevent @TraceID, 130, 29, @on exec sp_trace_setevent @TraceID, 130, 37, @on exec sp_trace_setevent @TraceID, 107, 7, @on exec sp_trace_setevent @TraceID, 107, 23, @on exec sp_trace_setevent @TraceID, 107, 8, @on exec sp_trace_setevent @TraceID, 107, 40, @on exec sp_trace_setevent @TraceID, 107, 64, @on exec sp_trace_setevent @TraceID, 107, 1, @on exec sp_trace_setevent @TraceID, 107, 9, @on exec sp_trace_setevent @TraceID, 107, 41, @on exec sp_trace_setevent @TraceID, 107, 49, @on exec sp_trace_setevent @TraceID, 107, 10, @on exec sp_trace_setevent @TraceID, 107, 26, @on exec sp_trace_setevent @TraceID, 107, 34, @on exec sp_trace_setevent @TraceID, 107, 42, @on exec sp_trace_setevent @TraceID, 107, 50, @on exec sp_trace_setevent @TraceID, 107, 3, @on exec sp_trace_setevent @TraceID, 107, 11, @on exec sp_trace_setevent @TraceID, 107, 35, @on exec sp_trace_setevent @TraceID, 107, 43, @on exec sp_trace_setevent @TraceID, 107, 51, @on exec sp_trace_setevent @TraceID, 107, 4, @on exec sp_trace_setevent @TraceID, 107, 12, @on exec sp_trace_setevent @TraceID, 107, 28, @on exec sp_trace_setevent @TraceID, 107, 60, @on exec sp_trace_setevent @TraceID, 107, 21, @on exec sp_trace_setevent @TraceID, 107, 29, @on exec sp_trace_setevent @TraceID, 107, 37, @on exec sp_trace_setevent @TraceID, 107, 6, @on exec sp_trace_setevent @TraceID, 107, 14, @on exec sp_trace_setevent @TraceID, 118, 7, @on exec sp_trace_setevent @TraceID, 118, 23, @on exec sp_trace_setevent @TraceID, 118, 8, @on exec sp_trace_setevent @TraceID, 118, 40, @on exec sp_trace_setevent @TraceID, 118, 64, @on exec sp_trace_setevent @TraceID, 118, 1, @on exec sp_trace_setevent @TraceID, 118, 9, @on exec sp_trace_setevent @TraceID, 118, 41, @on exec sp_trace_setevent @TraceID, 118, 49, @on exec sp_trace_setevent @TraceID, 118, 6, @on exec sp_trace_setevent @TraceID, 118, 10, @on exec sp_trace_setevent @TraceID, 118, 14, @on exec sp_trace_setevent @TraceID, 118, 26, @on exec sp_trace_setevent @TraceID, 118, 34, @on exec sp_trace_setevent @TraceID, 118, 50, @on exec sp_trace_setevent @TraceID, 118, 3, @on exec sp_trace_setevent @TraceID, 118, 11, @on exec sp_trace_setevent @TraceID, 118, 35, @on exec sp_trace_setevent @TraceID, 118, 51, @on exec sp_trace_setevent @TraceID, 118, 4, @on exec sp_trace_setevent @TraceID, 118, 12, @on exec sp_trace_setevent @TraceID, 118, 28, @on exec sp_trace_setevent @TraceID, 118, 60, @on exec sp_trace_setevent @TraceID, 118, 5, @on exec sp_trace_setevent @TraceID, 118, 21, @on exec sp_trace_setevent @TraceID, 118, 29, @on exec sp_trace_setevent @TraceID, 118, 37, @on exec sp_trace_setevent @TraceID, 114, 7, @on exec sp_trace_setevent @TraceID, 114, 23, @on exec sp_trace_setevent @TraceID, 114, 8, @on exec sp_trace_setevent @TraceID, 114, 40, @on exec sp_trace_setevent @TraceID, 114, 64, @on exec sp_trace_setevent @TraceID, 114, 1, @on exec sp_trace_setevent @TraceID, 114, 9, @on exec sp_trace_setevent @TraceID, 114, 41, @on exec sp_trace_setevent @TraceID, 114, 49, @on exec sp_trace_setevent @TraceID, 114, 2, @on exec sp_trace_setevent @TraceID, 114, 10, @on exec sp_trace_setevent @TraceID, 114, 26, @on exec sp_trace_setevent @TraceID, 114, 34, @on exec sp_trace_setevent @TraceID, 114, 50, @on exec sp_trace_setevent @TraceID, 114, 3, @on exec sp_trace_setevent @TraceID, 114, 11, @on exec sp_trace_setevent @TraceID, 114, 19, @on exec sp_trace_setevent @TraceID, 114, 35, @on exec sp_trace_setevent @TraceID, 114, 51, @on exec sp_trace_setevent @TraceID, 114, 59, @on exec sp_trace_setevent @TraceID, 114, 4, @on exec sp_trace_setevent @TraceID, 114, 12, @on exec sp_trace_setevent @TraceID, 114, 28, @on exec sp_trace_setevent @TraceID, 114, 44, @on exec sp_trace_setevent @TraceID, 114, 60, @on exec sp_trace_setevent @TraceID, 114, 5, @on exec sp_trace_setevent @TraceID, 114, 21, @on exec sp_trace_setevent @TraceID, 114, 29, @on exec sp_trace_setevent @TraceID, 114, 37, @on exec sp_trace_setevent @TraceID, 114, 6, @on exec sp_trace_setevent @TraceID, 114, 14, @on exec sp_trace_setevent @TraceID, 176, 7, @on exec sp_trace_setevent @TraceID, 176, 23, @on exec sp_trace_setevent @TraceID, 176, 8, @on exec sp_trace_setevent @TraceID, 176, 40, @on exec sp_trace_setevent @TraceID, 176, 64, @on exec sp_trace_setevent @TraceID, 176, 1, @on exec sp_trace_setevent @TraceID, 176, 9, @on exec sp_trace_setevent @TraceID, 176, 41, @on exec sp_trace_setevent @TraceID, 176, 49, @on exec sp_trace_setevent @TraceID, 176, 10, @on exec sp_trace_setevent @TraceID, 176, 26, @on exec sp_trace_setevent @TraceID, 176, 34, @on exec sp_trace_setevent @TraceID, 176, 50, @on exec sp_trace_setevent @TraceID, 176, 3, @on exec sp_trace_setevent @TraceID, 176, 11, @on exec sp_trace_setevent @TraceID, 176, 35, @on exec sp_trace_setevent @TraceID, 176, 51, @on exec sp_trace_setevent @TraceID, 176, 4, @on exec sp_trace_setevent @TraceID, 176, 12, @on exec sp_trace_setevent @TraceID, 176, 28, @on exec sp_trace_setevent @TraceID, 176, 60, @on exec sp_trace_setevent @TraceID, 176, 21, @on exec sp_trace_setevent @TraceID, 176, 29, @on exec sp_trace_setevent @TraceID, 176, 37, @on exec sp_trace_setevent @TraceID, 176, 45, @on exec sp_trace_setevent @TraceID, 176, 6, @on exec sp_trace_setevent @TraceID, 176, 14, @on exec sp_trace_setevent @TraceID, 176, 46, @on exec sp_trace_setevent @TraceID, 134, 7, @on exec sp_trace_setevent @TraceID, 134, 23, @on exec sp_trace_setevent @TraceID, 134, 39, @on exec sp_trace_setevent @TraceID, 134, 8, @on exec sp_trace_setevent @TraceID, 134, 40, @on exec sp_trace_setevent @TraceID, 134, 64, @on exec sp_trace_setevent @TraceID, 134, 1, @on exec sp_trace_setevent @TraceID, 134, 41, @on exec sp_trace_setevent @TraceID, 134, 49, @on exec sp_trace_setevent @TraceID, 134, 6, @on exec sp_trace_setevent @TraceID, 134, 10, @on exec sp_trace_setevent @TraceID, 134, 14, @on exec sp_trace_setevent @TraceID, 134, 26, @on exec sp_trace_setevent @TraceID, 134, 34, @on exec sp_trace_setevent @TraceID, 134, 42, @on exec sp_trace_setevent @TraceID, 134, 50, @on exec sp_trace_setevent @TraceID, 134, 3, @on exec sp_trace_setevent @TraceID, 134, 11, @on exec sp_trace_setevent @TraceID, 134, 35, @on exec sp_trace_setevent @TraceID, 134, 43, @on exec sp_trace_setevent @TraceID, 134, 51, @on exec sp_trace_setevent @TraceID, 134, 4, @on exec sp_trace_setevent @TraceID, 134, 12, @on exec sp_trace_setevent @TraceID, 134, 28, @on exec sp_trace_setevent @TraceID, 134, 60, @on exec sp_trace_setevent @TraceID, 134, 29, @on exec sp_trace_setevent @TraceID, 134, 37, @on exec sp_trace_setevent @TraceID, 170, 7, @on exec sp_trace_setevent @TraceID, 170, 23, @on exec sp_trace_setevent @TraceID, 170, 8, @on exec sp_trace_setevent @TraceID, 170, 40, @on exec sp_trace_setevent @TraceID, 170, 64, @on exec sp_trace_setevent @TraceID, 170, 1, @on exec sp_trace_setevent @TraceID, 170, 9, @on exec sp_trace_setevent @TraceID, 170, 41, @on exec sp_trace_setevent @TraceID, 170, 49, @on exec sp_trace_setevent @TraceID, 170, 10, @on exec sp_trace_setevent @TraceID, 170, 26, @on exec sp_trace_setevent @TraceID, 170, 34, @on exec sp_trace_setevent @TraceID, 170, 42, @on exec sp_trace_setevent @TraceID, 170, 50, @on exec sp_trace_setevent @TraceID, 170, 3, @on exec sp_trace_setevent @TraceID, 170, 11, @on exec sp_trace_setevent @TraceID, 170, 19, @on exec sp_trace_setevent @TraceID, 170, 35, @on exec sp_trace_setevent @TraceID, 170, 43, @on exec sp_trace_setevent @TraceID, 170, 51, @on exec sp_trace_setevent @TraceID, 170, 4, @on exec sp_trace_setevent @TraceID, 170, 12, @on exec sp_trace_setevent @TraceID, 170, 28, @on exec sp_trace_setevent @TraceID, 170, 60, @on exec sp_trace_setevent @TraceID, 170, 21, @on exec sp_trace_setevent @TraceID, 170, 29, @on exec sp_trace_setevent @TraceID, 170, 37, @on exec sp_trace_setevent @TraceID, 170, 6, @on exec sp_trace_setevent @TraceID, 170, 14, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Приложение SQL Server Profiler - 342699b4-dba3-411d-9901-e3ba875c146a' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Stock Planner' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Professional' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Viewer' exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_dbcmptlevel' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_update_job' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_add_job' exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_delete_job' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
После этого создаем задание в MSSQL
Я делаю в задание первым шагом удаление трасировок:
declare @trace_num int set @trace_num = (Select traceid from ::fn_trace_getinfo(default) where property = 2 and convert (varchar (500),value) like '%tracE'+convert(char(8),getdate(),112)+'%') select @trace_num --ставит на паузу потом стопает EXEC sp_trace_setstatus @traceid =@trace_num , @status = 0 EXEC sp_trace_setstatus @traceid =@trace_num , @status = 2
Второй шаг добавление трасировки :
Третьй и четвертый шаг отправка письма с удачным завершением или нет.
Чтобы посомтреть файл трасировки можно открыть в самом SQL Server Profiler или использовать код:
SELECT * FROM fn_trace_gettable(N'\\192.168.0.38\Backup\audit_rx6600\TracE20160125 094104.trc',DEFAULT)
Старт задания по запуску sql
Фалы: адуит через профайл
Посмотреть задание по id
SELECT * from msdb.dbo.sysjobs where job_id = N'5a082f0b-d52b-4f5c-a4b8-dcb6b5aeb6c0';