SQL Server – Server Side Trace

Eventualmente surgem demandas ou incidentes que exigem uma verificação mais apurada do ambiente do que está acontecendo em um determinado momento em sua instância SQL Server.

O SQL Server Profiler é uma excelente opção para monitorar seu ambiente e capturar diversos tipos de eventos dentro de sua instância ou base. Um pequeno porém é que esta ferramenta em seu modo gráfico, é demasiadamente pesada e onerosa para o seu servidor, podendo em determinadas situações até mesmo derrubar o serviço do SQL Server e em um ambiente crítico, isto certamente lhe trará grandes problemas.

Uma excelente opção a este cenário, onde existe a real necessidade de utilização da ferramenta, sem que haja o risco de degradação da performance do servidor, é a criação e utilização do trace em modo texto, executando o sem o uso da ferramenta gráfica.

Abaixo, um exemplo de um Trace Server Side e configurá-lo:

1 – Inicie o SQL Server Profiler normalmente, como se fosse executar a captura utilizando a ferramenta em modo gráfico.

2 – Crie um novo trace (File > New Trace), escolha suas definições, clique em “Run” e log em seguida pare o trace clicando em “Stop”.

3 – O próximo passo é exportar as definições do trace para criar então o script para geração do Trace Server Side. Para isto clique em File > Export > Script Trace Definition > For SQL Server 2005 – SQL11…

Salve o arquivo gerado com a extensão .sql em um diretório de sua preferência e abra o mesmo no SSMS (SQL Server Management Studio). Edite os campos necessário, conforme instruções nos comentários do próprio script gerado.

Basicamente, é necessário a alteração apenas do caminho onde o trace será salvo. Importante que este caminho deve conter o formado “C:tempArquivo”, não sendo necessário adicionar a extensão do arquivo, pois isto será feito automaticamente.

/****************************************************/

/* Created by: SQL Server 2012 Profiler */

/* Date: 23/03/2013 23:54:48 */

/****************************************************/

— Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5

— Please replace the text InsertFileNameHere, with an appropriate

— filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension

— will be appended to the filename automatically. If you are writing from

— remote server to local drive, please use UNC path and make sure server has

— write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’C:TempArquivoTraceExemplo’, @maxfilesize, NULL 

if (@rc != 0) goto error

— Client side File and Table cannot be scripted

— Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 14, 1, @on

exec sp_trace_setevent @TraceID, 14, 9, @on

exec sp_trace_setevent @TraceID, 14, 10, @on

exec sp_trace_setevent @TraceID, 14, 11, @on

exec sp_trace_setevent @TraceID, 14, 6, @on

exec sp_trace_setevent @TraceID, 14, 12, @on

exec sp_trace_setevent @TraceID, 14, 14, @on

exec sp_trace_setevent @TraceID, 15, 11, @on

exec sp_trace_setevent @TraceID, 15, 6, @on

exec sp_trace_setevent @TraceID, 15, 9, @on

exec sp_trace_setevent @TraceID, 15, 10, @on

exec sp_trace_setevent @TraceID, 15, 12, @on

exec sp_trace_setevent @TraceID, 15, 13, @on

exec sp_trace_setevent @TraceID, 15, 14, @on

exec sp_trace_setevent @TraceID, 15, 15, @on

exec sp_trace_setevent @TraceID, 15, 16, @on

— Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler – 83517218-d149-4719-9358-910ef72a53b9′

— 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

Após este procedimento basta executar o script acima no SSMS e seu trace server side estará em execução.

Para verificar a existência e status do mesmo, basta executar a consulta abaixo:

select * from sys.traces

O id “1” indica o Default Trace, presente por padrão em todas as instâncias. O id “2” indica o trace que foi criado, seu status (1 = Ativo e executando) e o caminho do mesmo (já com a extensão, conforme informado previamente) …

Por enquanto é isto, no próximo post, falarei sobre as procedures e functions para acesso e manipulação de Traces Server Side.

Abraço…

 
Edvaldo Castro

MCITP | MCTS | MCT | MTA | MTAC | CCSQLA | TOEIC

@edvaldocastro02