Création de fichiers de trace SQL Server Profiler côté serveur

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 07/10/2009  23:03:19         */
/****************************************************/
 
 
-- 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'InsertFileNameHere', @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, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
 
set @bigintfilter = 100
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter
 
-- 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

-------------------------------
-- 12/10/2009 - Nicolas SOUQUET
-------------------------------
ALTER PROCEDURE PsCreeTrace
    @_cheminFichierTrace NVARCHAR(256),
    @_minutesTrace SMALLINT,    -- Spécifie le nombre de minutes de durée de la trace <=> périodicité de renouvellement des fichiers de la trace
    @_avecDetails BIT = 0,        -- Retourne quelques caractéristiques de la trace créée
    @_avecIntegration BIT = 0    -- Intègre le fichier de trace précédemment créé directement de base de données locale
AS
BEGIN
    SET NOCOUNT ON
    --    
    BEGIN TRY
        DECLARE @IDTrace INT,
                @IDTraceToStop INT,
                @cheminFichierTraceToStop NVARCHAR(256),
                @timestampFichierTrace NCHAR(15),
                @maxFileSize BIGINT,
                @errMsg NVARCHAR(2047),
                @DOS VARCHAR(255)
 
        -- Vérifie que le chemin du fichier est :
        -- - soit un fichier d'un dossier local
        -- - soit un fichier d'un partage réseau
        IF
        (
            @_cheminFichierTrace NOT LIKE '_:\%' -- Répertoire local
            AND @_cheminFichierTrace NOT LIKE '\\%\%' -- Chemin UNC
        )
        BEGIN
            RAISERROR('Chemin de fichier de trace incorrect ("C:\monRepertoire\maTrace.trc" ou "\\monServeur\monRepertoirePartagé\monRepertoire\maTrace.trc"', 16, 1)
        END
 
        -----------------------------------------
        -- Gestion des traces précédemment créées
        -----------------------------------------
        -- Recherche s'il existe une trace de même nom de fichier (mais avec un "timestamp" différent)
        -- Arrête cette trace le cas échéant
        SELECT @IDTraceToStop = id,
                @cheminFichierTraceToStop = path
        FROM sys.traces
        WHERE path LIKE '%' + @_cheminFichierTrace + '%'
 
        IF @IDTraceToStop IS NOT NULL
        BEGIN            
            -- >>>>> ATTENTION ceci ne supprime pas le fichier de trace
            DECLARE @codeRetourArretTrace TINYINT
 
             -- Arrête la trace
            EXEC @codeRetourArretTrace = sys.sp_trace_setstatus @IDTraceToStop, 0
            IF @codeRetourArretTrace = 0
            BEGIN
                -- Ferme la trace et supprime sa définition du serveur.
                EXEC @codeRetourArretTrace = sys.sp_trace_setstatus @IDTraceToStop, 2
            END
            ELSE
            BEGIN
                SET @errMsg = CASE @codeRetourArretTrace
                                    WHEN 1 THEN 'Erreur inconnue'
                                    WHEN 8 THEN 'L''état spécifié n''est pas valide'
                                    WHEN 9 THEN 'Le descripteur de trace spécifié n''est pas valide'
                                    WHEN 13 THEN 'Mémoire insuffisante'
                                END
 
                RAISERROR('Problème lors de l''arrêt de la trace %s : %s', 16, 1, @_cheminFichierTrace, @errMsg)
            END
 
            -- Intègre le fichier dans une table de trace, le cas échéant
            IF
            (
                @codeRetourArretTrace = 0
                AND @_avecIntegration = 1
            )
            BEGIN
                INSERT INTO dbo.maTabledeTrace
                SELECT *
                FROM sys.fn_trace_gettable(@cheminFichierTraceToStop, NULL)
 
                -- Si l'intégration s'est bien passée, suppression du fichier de trace
                IF @@ERROR = 0
                BEGIN
                    SET @DOS = 'DEL ' + @cheminFichierTraceToStop
 
                    PRINT @DOS
 
                    EXEC master.dbo.xp_cmdshell @DOS, NO_OUTPUT -- A remplacer par l'appel à une procédure stockée d'assembly
                END
            END
        END    
 
        -- Supprime l'extension de fichier de trace
        -- celle-ci est automatiquement ajoutée à la création de la trace
        IF RIGHT(@_cheminFichierTrace, 4) = '.trc'
        BEGIN
            SELECT @_cheminFichierTrace = REPLACE(@_cheminFichierTrace, '.trc', '')
        END
 
        -- Calcule un "timestamp" pour le nom du fichier de trace (Format : YYYYMMDD_HHmmss)
        SELECT @timestampFichierTrace = CONVERT(NCHAR(8), GETDATE(), 112) + '_' +
                                        REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 108), ':', ''),
                                        @maxFileSize = 200 -- taille maximale du fichier de trace, en Mo
 
        -- Concatène le nom du fichier de trace avec le "timestamp"
        SET @_cheminFichierTrace = @_cheminFichierTrace + @timestampFichierTrace
 
        -----------------------
        -- Création de la trace
        -----------------------
        -- Calcule l'heure d'arrêt de la trace
        DECLARE @heureArretTrace DATETIME
        SELECT @heureArretTrace = DATEADD(minute, @_minutesTrace, GETDATE())
 
        EXEC sys.sp_trace_create
            @IDTrace OUTPUT, -- nécessaire pour paramétrer la trace par la suite
            0, -- permet de spécifier quelques options. Voir la BOL.
            @_cheminFichierTrace, -- l'extension .trc est automatiquement ajoutée
            @maxFileSize,
            @heureArretTrace,
            NULL -- nombre maximal de fichiers pour la même trace
 
        -----------------------------
        -- Attachement des événements
        -----------------------------
        -- Permet a posteriori de désactiver un événement
        -- alors que la trace est toujours ne cours d'exécution
        -- en passant 0 en dernier paramètre à sys.sp_trace_setevent
        DECLARE @actif BIT SET @actif = 1
 
        -- Événement : Deadlock graph
        EXEC sys.sp_trace_setevent @IDTrace, 148, 1, @actif
        EXEC sys.sp_trace_setevent @IDTrace, 148, 12, @actif    -- colonne SPID, obligatoire
        EXEC sys.sp_trace_setevent @IDTrace, 148, 14, @actif    -- colonne StartTime
 
        -- Evénement : Lock:Timeout (timeout> 0)
        EXEC sys.sp_trace_setevent @IDTrace, 189, 1, @actif        -- colonne TextData
        EXEC sys.sp_trace_setevent @IDTrace, 189, 12, @actif    -- colonne SPID, obligatoire
        EXEC sys.sp_trace_setevent @IDTrace, 189, 13, @actif    -- colonne Duration
        EXEC sys.sp_trace_setevent @IDTrace, 189, 14, @actif    -- colonne StartTime
 
        -- Événement : RPC:Completed
        EXEC sys.sp_trace_setevent @IDTrace, 10, 1, @actif        -- colonne TextData
        EXEC sys.sp_trace_setevent @IDTrace, 10, 12, @actif        -- colonne SPID, obligatoire
        EXEC sys.sp_trace_setevent @IDTrace, 10, 13, @actif        -- colonne Duration
        EXEC sys.sp_trace_setevent @IDTrace, 10, 14, @actif        -- colonne StartTime
        EXEC sys.sp_trace_setevent @IDTrace, 10, 16, @actif        -- colonne Reads
        EXEC sys.sp_trace_setevent @IDTrace, 10, 17, @actif        -- colonne Writes
 
        -----------------------------------
        -- Ajout des filtres aux événements
        -----------------------------------        
        -- Le type de la valeur à filtrer doit correspondre à celui de la colonne
        -- pour trouver le type de la colonne, voir plus loin
        -- Ici, le type de la colonne "Reads" est BIGINT
        DECLARE @bigintFilter BIGINT SET @bigintFilter = 100
 
        EXEC sys.sp_trace_setfilter
            @IDTrace,        -- identifiant de la trace
            16,                -- colonne de la trace
            0,                -- AND : 0 | 1 : OR
            4,                -- >=
            @bigintFilter    -- valeur du filtre
 
        ------------------------
        -- Démarrage de la trace
        ------------------------
        EXEC sys.sp_trace_setstatus @IDTrace, 1
 
        -------------------------------------------------
        -- Affiche éventuellement les détails de la trace
        -------------------------------------------------
        IF @_avecDetails = 1
        BEGIN
            SELECT id,
                    CASE status
                        WHEN 0 THEN 'Arrêtée'
                        WHEN 1 THEN 'Démarrée'
                    END AS Statut,
                    path AS Emplacement,
                    stop_time AS DateHeureArret
            FROM sys.traces
            WHERE id = @IDTrace
        END
    END TRY
    BEGIN CATCH
        IF EXISTS
        (
            SELECT *
            FROM sys.traces
            WHERE id = @IDTrace
        )
        BEGIN
            DECLARE @arretTraceOK BIT = 0
            BEGIN TRY
                -- >>>>> ATTENTION ceci ne supprime pas le fichier de trace
                EXEC sys.sp_trace_setstatus @IDTrace, 0 -- Arrête la trace
                EXEC sys.sp_trace_setstatus @IDTrace, 2 -- Ferme la trace et supprime sa définition du serveur.
 
                SET @arretTraceOK = 1
            END TRY
            BEGIN CATCH
                RAISERROR('Erreur à l''arrêt de la trace %s',16, 1, @_cheminFichierTrace)
            END CATCH
 
            IF @arretTraceOK = 1
            BEGIN
                -- Suppression du fichier de trace éventuel
                SET @DOS = 'DEL ' + @_cheminFichierTrace
                EXEC master.dbo.xp_cmdshell @DOS, NO_OUTPUT
 
                PRINT 'Arrêt de la trace ' + @_cheminFichierTrace + 'effectué '
            END
        END        
        -- Re-lève l'exception
        SET @errMsg = ERROR_MESSAGE()
        RAISERROR(@errMsg, 16, 1)
    END CATCH
END

DECLARE @req VARCHAR(256) SET @req = 'SELECT '
 
;WITH
    CTE_COLONNES_TRACE AS
    (
        SELECT DISTINCT columnid
        FROM sys.fn_trace_geteventinfo(2)
    )
SELECT @req = @req + C.name + ','
FROM sys.tables AS T
JOIN sys.columns AS C
    ON T.object_id = C.object_id
JOIN CTE_COLONNES_TRACE AS CCT
    ON C.column_id = CCT.columnid
WHERE T.name = 'maTableDeTrace'
 
SET @req = LEFT(@req, LEN(@req) - 1) + ' FROM dbo.maTableDeTrace'
EXEC (@req)