Прохождение теста Works with SQL Server 2008. Требование 3.

Требование 3. На сервере должна быть включена трассировка по умолчанию (default trace).

На тему механизма трассирования в SQL Server, я летом написал многословный пост в своем MSDNовском блоге, так что если кто-либо еще не сподобился поработать с трассировками и хочет в доступной форме узнать, что это такое, может его почитать.

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

select * from sys.traces

Скрипт 1

Та, у которой в графе is_default будет стоять 1, это будет она. Обычно она самая первая с id = 1, если вы ее до этого не касались. Трассы с NULL в графе path и 1 в поле is_rowset - это, скорее всего, те, что запущены у вас из профайлера. Требование 3 говорит, что служебная трасса должна быть запущена. По умолчанию, она на то и является служебной, что создается и запускается в момент старта сервера, так что если вы с ней специально ничего с ней плохого не делали, требование должно выполняться. Про служебную трассу имеется скупое упоминание в BOL: http://msdn.microsoft.com/ru-ru/library/ms175513.aspx, что совершенно незаслуженно по отношению к такому интересному объекту. Более подробно можно прочитать в статье Kalen Delaney - http://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html.

Проверить, что трасса стартована, можно, взглянув на колонку status в выдаче Скрипта 1. 1 означает, что она бежит, 0 - что стоит. Но это для обычной трассы. Если служебная трасса остановлена, в sys.traces ее просто нет. Кроме того, в отличие от обычной трассы, управление которой осуществляется при помощи процедуры sp_trace_setstatus @traceid = <id трассы>, @status = 1 (старт), 0 (стоп), 2 (убить), служебную трассу нельзя убить, а остановить/запустить можно только при помощи sp_configure. Так осуществляется ее останов:

exec sp_configure @configname = 'show advanced', @configvalue = 1

reconfigure

exec sp_configure @configname = 'default trace enabled', @configvalue = 0

reconfigure with override

Скрипт 2

А так, соответственно, запуск:

exec sp_configure @configname = 'default trace enabled', @configvalue = 1

reconfigure with override

Скрипт 3

Через sp_configure можно также посмотреть текущий статус трассы подобно тому, как мы смотрели его в поле status выдачи Скрипта 1.

exec sp_configure @configname = 'default trace enabled'

Скрипт 4

0 будет означать стояние, 1 - что она запущена.

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

declare @status tinyint

select top 1 @status = status from sys.traces where is_default = 1

if @status is null begin

 exec sp_configure @configname = 'default trace enabled', @configvalue = 1

 reconfigure

end

Скрипт 5

Обратите внимание, что проверка выполняется не на @status = 0, а @status is null, потому что, как отмечалось выше, отключение служебной трассы приводит не к изменению поля status, а к исчезанию ее из sys.traces. Это все, что касается Требования 3. Дальнейшее для общего развития.

События, которые собирает SQL Trace, он умеет выплевывать в два типа назначения: в файл и в rowset. Примером приемника первого типа является служебная трасса, второго - профайлер. В случае приемника второго типа возможна потеря событий, если они порождаются слишком быстро и rowset не успевает их разгребать. Создать приемник первого типа можно при помощи хранимой процедуры sp_trace_create. В параметрах указывается шаблон имени файла, максимальный размер, до которого он сможет расти, прежде чем трасса переметнется писать в новый файл, и размер скользящего окна, т.е. при заведении нового файла на сколько файлов нужно отсчитать назад, чтобы оттуда начать удаление старых файлов. Эти и другие атрибуты трассы можно посмотреть в sys.traces или при помощи функции master.sys.fn_trace_getinfo(), параметром которой служит идентификатор трассы (0 - для всех трасс). Чтобы не заглядывать всякий раз в документацию, вспоминая, что какое свойство значит, я вставил их в запрос. Кроме того, распайвотил результат, пустив названия свойств по колонкам. Для служебной трассы имеем:

with cte(TraceID, TraceOption, FileName, MaxSize_MB, StopTime, IsRunning)

as (select * from ::fn_trace_getinfo(1) pivot (min(value) for property in ([1], [2], [3], [4], [5])) pt)

select

TraceID,

case TraceOption when 2 then '2 = TRACE_FILE_ROLLOVER' when 4 then '4 = SHUTDOWN_ON_ERROR' when 6 then '6 = ROLLOVER_AND_SHUTDOWN' when 8 then '8 = PRODUCE_BLACKBOX' else TraceOption end,

FileName, MaxSize_MB, StopTime, IsRunning

from cte

 

TraceID	(No column name)	FileName	MaxSize_MB	StopTime	IsRunning
1	2 = TRACE_FILE_ROLLOVER	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_27.trc	20	NULL	1

Скрипт 6

 

Эта информация представляет собой подмножество из sys.traces.

Интерес также представляют события, собираемые служебной трассой и какая информация (колонки) собираются по каждому событию. Ответ дает функция master.sys.fn_trace_geteventinfo(). Чтобы получить события и колонки не в виде своих id, а в виде осмысленных описаний, ее надо сджойнить с DMV sys.trace_events и sys.trace_columns:

select f.eventid, e.name as EventName, e.category_id, cat.name as EventCategoryName, f.columnid, c.name as ColumnName

from ::fn_trace_geteventinfo(1) f

inner join sys.trace_events e on f.eventid = e.trace_event_id

inner join sys.trace_categories cat on e.category_id = cat.category_id

inner join sys.trace_columns c on f.columnid = c.trace_column_id

Скрипт 7

 

Аналогично, посмотреть, не навешаны ли на события трассы какие-либо фильтры, можно узнать из функции master.sys.fn_trace_getfilterinfo().

Прочитать результаты трассы можно функцией fn_trace_gettable(). В качестве первого параметра выступает шаблон файла, куда пишется трасса. В свойствах трассы (Скрипт 6) мы видели текущий файл, куда в данный момент пишется трасса - log_27.trc. 27 - это монотонно прирастающий порядковый номер файла трассы, значит, шаблон будет log.trc:

select * from master.sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc', default)

 Скрипт 8

Обратите внимание, что событие 14 (Audit Login - см. sys.trace_events) не собирается служебной трассой (поставьте в Скрипте 7 where f.eventid = 14). Довключить его туда нельзя. Структуру служебной трассы модифицировать нельзя. Можно только смотреть, какие события/колонки, фильтры в нее входят. Стало быть, для решения задачи мониторинга успешных попыток логона на сервер в предыдущем посте пришлось бы создавать отдельную трассу, не реши мы ее при помощи аудита. А то я было грешным делом подумал, что требование по дефолтной трассе нужно им, чтобы проверить остальные требования.

 

Алексей Шуленин, Microsoft