[SQL Server] Tracer le serveur
-- =============================================
-- Author: KHARROUBI Zouhaier
-- Create date: 17/03/2013
-- Description: Tracer le serveur SQLServer & Sauvegarder les traces dans une table
-- =============================================
ALTER PROCEDURE [dbo].[usp_Admin_Trace_SQLServer]
@NomFichierTrace NVARCHAR(256), -- Nom Fichier Trace
@cheminRepertoireTrace NVARCHAR(256),
@minutesTrace SMALLINT, -- Spécifie le nombre de minutes de durée de la trace <=> périodocité de 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écedémment 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),
@REQUETE VARCHAR(500),
@cheminFichierTrace NVARCHAR(256),
@MessageErreur NVARCHAR(256),
@NbreFichierTrace TINYINT
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#FichierTrace]'))
DROP TABLE #FichierTrace
SELECT @cheminFichierTrace = @cheminRepertoireTrace + @NomFichierTrace
CREATE TABLE #FichierTrace
(
Num_Fichier INT IDENTITY(1,1),
FichierTrace VARCHAR(60)
)
SELECT @REQUETE ='INSERT INTO #FichierTrace
EXEC master.sys.xp_cmdshell DIR /B "' + @cheminRepertoireTrace + '*.trc"'
EXEC @REQUETE
SELECT @NbreFichierTrace=COUNT(*) FROM #FichierTrace
-- 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
SELECT @MessageErreur = 'Chemin de fichier de trace incorrect "' + @cheminFichierTrace + '"'
RAISERROR(@MessageErreur, 16, 1)
END
-----------------------------------------
-- Gestion des traces précedemment 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
END
-- Intègrer le(s) fichier(s) dans une table de trace
WHILE @NbreFichierTrace >0
BEGIN
SELECT @cheminFichierTraceToStop=FichierTrace FROM #FichierTrace
WHERE Num_Fichier=@NbreFichierTrace AND FichierTrace IS NOT NULL
IF (@codeRetourArretTrace = 0 AND @avecIntegration = 1)
BEGIN
INSERT INTO dbo.JOURNAL_SQLSERVER
(
EventClass
,TextData
,SPID
,StartTime
,BinaryData
,DatabaseName
,ApplicationName
,ClientProcessID
,DatabaseID
,EventSequence
,GroupID
,HostName
,IsSystem
,LoginName
,LoginSid
,NTDomainName
,NTUserName
,RequestID
,ServerName
,SessionLoginName
,TransactionID
,XactSequence
,Duration
,EndTime
,ObjectName
)
SELECT
EventClass
,TextData
,SPID
,StartTime
,BinaryData
,DatabaseName
,ApplicationName
,ClientProcessID
,DatabaseID
,EventSequence
,GroupID
,HostName
,IsSystem
,LoginName
,LoginSid
,NTDomainName
,NTUserName
,RequestID
,ServerName
,SessionLoginName
,TransactionID
,XactSequence
,Duration
,EndTime
,ObjectName
FROM sys.fn_trace_gettable(@cheminFichierTraceToStop, NULL)
WHERE TextData IS NOT NULL
AND TextData NOT LIKE '%network protocol%'
ORDER BY StartTime
-- 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
SELECT @NbreFichierTrace = @NbreFichierTrace - 1
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 à posteriori de désactiver un événement
-- alors que la trace est toujours en cours d'exécution
-- en passant 0 en dernier paramètre à sys.sp_trace_setevent
DECLARE @actif BIT SET @actif = 1
-- Evé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
---- Evé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
/* 14 = "Audit Login" Se produit lorsqu'un utilisateur réussit à se connecter à SQL Server */
EXEC sp_trace_setevent@IDTrace, 14, 8, @actif
EXEC sp_trace_setevent@IDTrace, 14, 64, @actif
EXEC sp_trace_setevent@IDTrace, 14, 1, @actif
EXEC sp_trace_setevent@IDTrace, 14, 14, @actif
EXEC sp_trace_setevent@IDTrace, 14, 11, @actif
EXEC sp_trace_setevent@IDTrace, 14, 35, @actif
EXEC sp_trace_setevent@IDTrace, 14, 12, @actif
/* 15 = "Audit Logout" Se produit lorsqu'un utilisateur se déconnecte de SQL Server */
EXEC sp_trace_setevent@IDTrace, 15, 15, @actif
EXEC sp_trace_setevent@IDTrace, 15, 8, @actif
EXEC sp_trace_setevent@IDTrace, 15, 64, @actif
EXEC sp_trace_setevent@IDTrace, 15, 14, @actif
EXEC sp_trace_setevent@IDTrace, 15, 11, @actif
EXEC sp_trace_setevent@IDTrace, 15, 35, @actif
EXEC sp_trace_setevent@IDTrace, 15, 12, @actif
EXEC sp_trace_setevent@IDTrace, 15, 13, @actif
/* 17 = "ExistingConnection" Détecte toutes les activités des utilisateurs connectés à SQL Server avant le démarrage de la trace */
EXEC sp_trace_setevent@IDTrace, 17, 8, @actif
EXEC sp_trace_setevent@IDTrace, 17, 64, @actif
EXEC sp_trace_setevent@IDTrace, 17, 1, @actif
EXEC sp_trace_setevent@IDTrace, 17, 14, @actif
EXEC sp_trace_setevent@IDTrace, 17, 11, @actif
EXEC sp_trace_setevent@IDTrace, 17, 35, @actif
EXEC sp_trace_setevent@IDTrace, 17, 12, @actif
/* 11 = "RPC:Starting" Se produit lorsqu'un appel de procédure distante a commencé */
EXEC sp_trace_setevent@IDTrace, 11, 8, @actif
EXEC sp_trace_setevent@IDTrace, 11, 64, @actif
EXEC sp_trace_setevent@IDTrace, 11, 1, @actif
EXEC sp_trace_setevent@IDTrace, 11, 2, @actif
EXEC sp_trace_setevent@IDTrace, 11, 10, @actif
EXEC sp_trace_setevent@IDTrace, 11, 14, @actif
EXEC sp_trace_setevent@IDTrace, 11, 26, @actif
EXEC sp_trace_setevent@IDTrace, 11, 34, @actif
EXEC sp_trace_setevent@IDTrace, 11, 50, @actif
EXEC sp_trace_setevent@IDTrace, 11, 11, @actif
EXEC sp_trace_setevent@IDTrace, 11, 35, @actif
EXEC sp_trace_setevent@IDTrace, 11, 51, @actif
EXEC sp_trace_setevent@IDTrace, 11, 12, @actif
/* 13 = "SQL:BatchStarting" Se produit lorsqu'un lot d'instructions Transact-SQL a démarré */
EXEC sp_trace_setevent@IDTrace, 13, 7, @actif
EXEC sp_trace_setevent@IDTrace, 13, 8, @actif
EXEC sp_trace_setevent@IDTrace, 13, 64, @actif
EXEC sp_trace_setevent@IDTrace, 13, 1, @actif
EXEC sp_trace_setevent@IDTrace, 13, 9, @actif
EXEC sp_trace_setevent@IDTrace, 13, 41, @actif
EXEC sp_trace_setevent@IDTrace, 13, 49, @actif
EXEC sp_trace_setevent@IDTrace, 13, 6, @actif
EXEC sp_trace_setevent@IDTrace, 13, 10, @actif
EXEC sp_trace_setevent@IDTrace, 13, 14, @actif
EXEC sp_trace_setevent@IDTrace, 13, 26, @actif
EXEC sp_trace_setevent@IDTrace, 13, 50, @actif
EXEC sp_trace_setevent@IDTrace, 13, 66, @actif
EXEC sp_trace_setevent@IDTrace, 13, 3, @actif
EXEC sp_trace_setevent@IDTrace, 13, 11, @actif
EXEC sp_trace_setevent@IDTrace, 13, 35, @actif
EXEC sp_trace_setevent@IDTrace, 13, 51, @actif
EXEC sp_trace_setevent@IDTrace, 13, 4, @actif
EXEC sp_trace_setevent@IDTrace, 13, 12, @actif
EXEC sp_trace_setevent@IDTrace, 13, 60, @actif
-----------------------------------
-- 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
DROP TABLE #FichierTrace
END