Clonando scripts de traces no SQL Server

Neste artigo, irei mostrar uma forma simples e prática de “clonar” traces em execução no SQL Server.

Este script irá obter todas as configurações básicas (caminho do trace, data de término, etc), configurações dos eventos monitorados, e também os todos os filtros configurados de acordo com parametrização TRACEID.

Este script basicamente obtém todas as informações através das views e das functions disponíveis no SQL Server. As documentações utilizadas para desenvolvimento do script estão logo abaixo:

O parâmetro utilizado para clonagem do trace é o @TraceID. Este parâmetro é o ID do trace em execução, na qual você precisa clonar.

Para obter o ID/Trace em execução, execute:

SELECT * FROM sys.traces

Para clonar o trace, execute o script a seguir:

USE master 
BEGIN

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE @Path VARCHAR(300) = 'MeuTrace'
DECLARE @TraceID INT = 1
DECLARE @Resultados TABLE
(
    IDLinha INT IDENTITY(1,1),
    LinhaComando VARCHAR(MAX)
)

If(NOT EXISTS(SELECT 1 FROM sys.traces WHERE id = @TraceID))
Begin
    RAISERROR('* Trace (ID = %i) não encontrado! Chece a sys.traces para mais detalhes.', 0, 1, @TraceID) WITH NOWAIT;
    RETURN
End

INSERT INTO @Resultados(LinhaComando) 
    SELECT '--Declaração de variáveis para parametrização do TRACE ' UNION ALL
    SELECT 'DECLARE @TraceIDOut       INT           ' UNION ALL
    SELECT 'DECLARE @Options          INT           ' UNION ALL
    SELECT 'DECLARE @Path             NVARCHAR(256) ' UNION ALL 
    SELECT 'DECLARE @Maxfilesize      BIGINT        ' UNION ALL
    SELECT 'DECLARE @MaxRolloverFiles INT           ' UNION ALL
    SELECT 'DECLARE @StopTime         DATETIME      ' UNION ALL
    SELECT 'DECLARE @On               BIT           ' UNION ALL
    SELECT '                                        ' UNION ALL
    SELECT 'Set @On = 1 -- Para fins de script, acho melhor sempre definir um script para iniciar o rastreamento após a criação. '

-- Script das configurações da sys.traces
INSERT INTO @Resultados(LinhaComando) 
    SELECT 'set @maxfilesize = ' + CASE WHEN max_size IS NULL THEN '20' ELSE CONVERT(varchar,max_size) END + ' --size in MB ' from sys.traces WHERE id =@TraceID

INSERT INTO @Resultados(LinhaComando) 
    SELECT 'Set @MaxRolloverFiles = ' + CASE WHEN max_files IS NULL THEN ' 5 ' ELSE CONVERT(VARCHAR, max_files) END + 
        '  --Número de arquivos; ou seja, com 5 arquivos, ele sobrescreve os arquivos anteriores ' from sys.traces WHERE id =@TraceID

INSERT INTO @Resultados(LinhaComando) 
    SELECT 'Set @Stoptime = ' + CASE WHEN stop_time IS NULL THEN 'NULL' ELSE '''' + 
        CONVERT(VARCHAR(40), stop_time, 121)+ '''' END + '  -- NULL significa que não há fim, caso contrário, especifique uma data ' FROM sys.traces WHERE id = @TraceID

INSERT INTO @Resultados(LinhaComando) 
    SELECT 'Set @Options = 2' -- Sobrescreva, ignore todas as opções anteriores

INSERT INTO @Resultados(LinhaComando) 
    SELECT  'Set @Path  = '''  + CASE WHEN [path] IS NULL THEN @Path ELSE Left([path], Len([path]) - 4) END + '''' + 
            '  -- O trace adiciona ".trc" ao nome completo do arquivo, por isso evite "name.trc.trc", remova-o do teu script ' FROM sys.traces WHERE id = @TraceID

INSERT INTO @Resultados(LinhaComando) 
    SELECT '' 

/*
    DOC:
    sp_trace_create [ @TraceID = ] trace_id OUTPUT 
        , [ @options = ] option_value 
        , [ @tracefile = ] 'trace_file' 
        [ , [ @maxfilesize = ] max_file_size ]
        [ , [ @stoptime = ] 'stop_time' ]
        [ , [ @filecount = ] 'max_rollover_files' ]
*/

INSERT INTO @Resultados(LinhaComando) 
    SELECT '  --Cria o trace... '   

INSERT INTO @Resultados(LinhaComando) 
    SELECT '--Cria o trace ' UNION ALL
    SELECT 'EXEC sp_trace_create ' UNION ALL
    SELECT '    @TraceID           = @TraceIDOut OUTPUT, ' UNION ALL
    SELECT '    @options           = @Options, ' UNION ALL
    SELECT '    @tracefile         = @Path, ' UNION ALL
    SELECT '    @maxfilesize       = @Maxfilesize, ' UNION ALL
    SELECT '    @stoptime          = @StopTime, ' UNION ALL
    SELECT '    @filecount         = @MaxRolloverFiles ' 

--Detalhes
INSERT INTO @Resultados(LinhaComando) 
    SELECT ''

INSERT INTO @Resultados(LinhaComando) 
    SELECT  '  --Para o evento de todas instruções SQL concluídas, capture colunas de dados acessíveis  ' 
        --EXEMPLO: exec sp_trace_setevent @TraceIDout, 12, 6, @on --SQL:BatchCompleted,NTUserName    

INSERT INTO @Resultados(LinhaComando) 
    SELECT 
            '  EXEC sp_trace_setevent @TraceIDout,' + CONVERT(VARCHAR(MAX), X.eventid) + ',' +  CONVERT(VARCHAR(MAX),X.columnid) + ',@On -- ' +  E.[Name] + ',' + V.[name]
        FROM  ::fn_trace_geteventinfo(@TraceID) AS X
            JOIN sys.trace_events E ON X.eventid  = E.trace_event_id
            JOIN sys.trace_columns V ON X.columnid = V.trace_column_id

INSERT INTO @Resultados(LinhaComando) 
    SELECT '--Filtros'

INSERT INTO @Resultados(LinhaComando) 
    SELECT ''

INSERT INTO @Resultados(LinhaComando) 
    SELECT 
            '  EXEC sp_trace_setfilter  traceidout' + ',' + CONVERT(VARCHAR, X.columnid) + 
            ',' + CONVERT(VARCHAR, logical_operator) + 
            ',' + CONVERT(VARCHAR, comparison_operator) + ',' +  
            ' N''' + CONVERT(VARCHAR(8000), [value]) + ''' ' + 
            '  -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR ' END + V.[name] + 
            CASE 
                WHEN comparison_operator = 0  THEN ' = '
                WHEN comparison_operator = 1  THEN ' <> '
                WHEN comparison_operator = 2  THEN '  > '
                WHEN comparison_operator = 3  THEN '  <  '
                WHEN comparison_operator = 4  THEN '  >=  '
                WHEN comparison_operator = 5  THEN ' <=  '
                WHEN comparison_operator = 6  THEN '  LIKE  '
                WHEN comparison_operator = 7  THEN ' NOT LIKE  '
            END + CONVERT(VARCHAR(8000), [value]) 
    FROM ::fn_trace_getfilterinfo(@TraceID) X 
        JOIN sys.trace_columns V ON X.columnid = V.trace_column_id

INSERT INTO @Resultados(LinhaComando) 
    SELECT '---Passo final'

INSERT INTO @Resultados(LinhaComando) 
    SELECT ''

INSERT INTO @Resultados(LinhaComando) 
    SELECT '--Inicializa trace '  

INSERT INTO @Resultados(LinhaComando) 
    SELECT '  exec sp_trace_setstatus @TraceIDout, 1 ---Inicializa trace   ' 

INSERT INTO @Resultados(LinhaComando)
    SELECT '  --exec sp_trace_setstatus TRACEID, 0 ---Para o trace, você deve saber qual é o TRACEID (@TraceIDOut) para pará-lo   ' 

INSERT INTO @Resultados(LinhaComando) 
    SELECT '  --exec sp_trace_setstatus TRACEID, 2 ---Remove o trace, você deve saber qual é o TRACEID (@TraceIDOut) para removê-lo  ' 

SELECT LinhaComando
    FROM @Resultados 
    ORDER BY IDLinha

END

O resultado final:

Copie e cole em uma nova aba (ou query), e execute logo em seguida.

Caso tenham alguma dúvida sobre o script, sintam-se a vontade para colocar nos comentários!

Espero ter ajudado!

Deixe um comentário