SelectEtoile : Base de connaissance SGBD

Posts Tagged 'sptracesetfilter'

A propos des Trace Profiler

Plusieurs possibilités sont disponibles pour lancer une trace SQL Server. On peut gérer avec l'outil SQL Server Profiler ou directement en ligne de commande.

Gestion des Traces avec SQL Server Profiler

 Il existe pleins d'articles sur le sujet sur le net...

 

Gestion d'une Trace en ligne de commande

Création d'une trace

declare @TraceFileName nvarchar(256)
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
set @TraceFileName = 'D:\admin\logs\SQLServerJPS.trc'

exec sp_trace_create @TraceID output, 0, @TraceFileName, @maxfilesize, NULL 

 

Visualisation des Traces existantes
select * from sys.traces

Exemple de résultat :

id    status    path    max_size    stop_time    max_files  

1    1    D:\SQLSERVER\MSSQL10.SQLSERVER\MSSQL\Log\log_8.trc    20    NULL    5

2    1    C:\Temp\TraceAuditLogin_SQLSERVER.trc    50    NULL    1   

3    0    D:\admin\logs\SQLServerJPS.trc.trc    50    NULL    1

Ici, nous avons 2 traces. La trace avec l'id 1 est la trace par défaut de SQLServer (Voir paragraphe en dessous).

La trace 2 est active (status=1) et se fait dans un fichier 'C:\Temp\TraceAuditLogin_SQLSERVER.trc'.

Notre trace est la 3. Elle est d'ailleurs inactive pour le moment. Il nous faut, avant de l'activer, définir les événements que l'on veut tracer.

 

Définition des événements à tracer

La procédure décrite ci dessous permet d'ajouter des événements sur une trace. On va pouvoir ajouter / supprimer des événements.

Les tables de descriptions des différents événements sont développées ici

Exemple :

declare @on bit
declare @TraceID int
set @TraceID = 3
set @on = 1
    exec sp_trace_setevent @TraceID, 14, 3, @on
    exec sp_trace_setevent @TraceID, 14, 11, @on
    exec sp_trace_setevent @TraceID, 14, 23, @on
    exec sp_trace_setevent @TraceID, 14, 8, @on
    exec sp_trace_setevent @TraceID, 14, 10, @on
    exec sp_trace_setevent @TraceID, 14, 12, @on
    exec sp_trace_setevent @TraceID, 14, 14, @on
    exec sp_trace_setevent @TraceID, 14, 26, @on
    exec sp_trace_setevent @TraceID, 14, 9, @on
    exec sp_trace_setevent @TraceID, 20, 3, @on
    exec sp_trace_setevent @TraceID, 20, 7, @on
    exec sp_trace_setevent @TraceID, 20, 11, @on
    exec sp_trace_setevent @TraceID, 20, 15, @on
    exec sp_trace_setevent @TraceID, 20, 23, @on
    exec sp_trace_setevent @TraceID, 20, 8, @on
    exec sp_trace_setevent @TraceID, 20, 12, @on
    exec sp_trace_setevent @TraceID, 20, 9, @on
    exec sp_trace_setevent @TraceID, 20, 6, @on
    exec sp_trace_setevent @TraceID, 20, 10, @on
    exec sp_trace_setevent @TraceID, 20, 14, @on
    exec sp_trace_setevent @TraceID, 20, 26, @on
    exec sp_trace_setevent @TraceID, 15, 7, @on
    exec sp_trace_setevent @TraceID, 15, 15, @on
    exec sp_trace_setevent @TraceID, 15, 23, @on
    exec sp_trace_setevent @TraceID, 15, 8, @on
    exec sp_trace_setevent @TraceID, 15, 12, @on
    exec sp_trace_setevent @TraceID, 15, 9, @on
    exec sp_trace_setevent @TraceID, 15, 6, @on
    exec sp_trace_setevent @TraceID, 15, 10, @on
    exec sp_trace_setevent @TraceID, 15, 14, @on
    exec sp_trace_setevent @TraceID, 15, 26, @on
    exec sp_trace_setevent @TraceID, 15, 3, @on
    exec sp_trace_setevent @TraceID, 15, 11, @on

 

Activer notre trace
declare @TraceID int
set @TraceID = 3
exec sp_trace_setstatus @TraceID, 1

Vérification

select * from sys.traces

Exemple de résultat :

id    status    path    max_size    stop_time    max_files  

1    1    D:\SQLSERVER\MSSQL10.SQLSERVER\MSSQL\Log\log_8.trc    20    NULL    5

2    1    C:\Temp\TraceAuditLogin_SQLSERVER.trc    50    NULL    1   

3    1    D:\admin\logs\SQLServerJPS.trc.trc    50    NULL    1

On voit alors que notre trace est active.

 

 

Filtrer une trace

Exemple:

set @intfilter = 2
exec sp_trace_setfilter @TraceID, 21, 0, 1, @intfilter

 

Supprimer une trace

declare @TraceID int
set @TraceID = 3
exec sp_trace_setstatus @TraceID, 2

-- To check

select * from sys.traces

 

Trace par défaut

Par défaut depuis SQLServer version 2005, il est paramétré automatiquement une trace.

Pour le vérifier taper les commandes suivantes :

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'default trace enabled'
go

 

-- For informations about the default trace

SELECT * FROM ::fn_trace_getinfo(default)

 

 

Les tables et fonctions systèmes

sys.traces : liste les traces et leur statut

sys.trace_events : événements à rattacher aux différentes traces

fn_trace_getinfo() : info sur une trace (équivalent à une mise en forme de sys.traces)

fn_trace_gettable()

 

Exemple complet d'une trace :

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 01/25/2013  08:09:18 PM         */
/****************************************************/


-- 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:\MyFolder\MyTrace. 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'd:\perfmon\traceprofiler_liqor', @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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 96abcad4-651d-4c18-a711-7399bcf6cd47'
-- 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