1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
| CREATE PROCEDURE [dbo].[spJOB_MONITOR_USER_COMMANDS]
AS
BEGIN
DECLARE @rc INT;
DECLARE @TraceID INT;
-- Calcule le nom du fciher de trace
DECLARE @filename NVARCHAR(245);
SELECT @filename = '[LECTEUR]:\RepertoireDeTraces\' + DB_NAME() + '_' +
CONVERT(VARCHAR, DATEPART(year, GETDATE())) + '_' +
CASE LEN(CONVERT(VARCHAR, DATEPART(month, GETDATE())))
WHEN 1 THEN '0' + CONVERT(VARCHAR, DATEPART(month, GETDATE()))
ELSE CONVERT(VARCHAR, DATEPART(month, GETDATE()))
END + '_' +
CONVERT(VARCHAR, DATEPART(day, GETDATE())) + '_' +
CONVERT(VARCHAR, DATEPART(hour, GETDATE())) + 'H' +
CONVERT(VARCHAR, DATEPART(minute, GETDATE())) + 'min' +
CONVERT(VARCHAR, DATEPART(second, GETDATE())) + 's';
DECLARE @STOP_TIME DATETIME;
SELECT @STOP_TIME = DATEADD(Hour, 12, GETDATE());
DECLARE @trace_file_size BIGINT; SET @trace_file_size = 50;
-- Crée le fichier de trace
-- sp_trace_create @traceid OUTPUT, @options, @trace_file, @maxfilesize, @stoptime, @filecount
EXEC @rc = sp_trace_create @TraceID OUTPUT, 6, @filename, @trace_file_size, @STOP_TIME, NULL
DECLARE @ERR VARCHAR(128);
SELECT @ERR = CASE @rc
WHEN 0 THEN 'sp_trace_create: created TRACEID ' + CONVERT(VARCHAR, @TraceID)
WHEN 1 THEN 'sp_trace_create: UNKNOWN ERROR'
WHEN 10 THEN 'sp_trace_create: INVALID OPTIONS'
WHEN 12 THEN 'sp_trace_create: FILE NOT CREATED'
WHEN 13 THEN 'sp_trace_create: OUT OF MEMORY'
WHEN 14 THEN 'sp_trace_create: INVALID STOP TIME'
WHEN 15 THEN 'sp_trace_create: INVALID PARAMETERS'
END;
-- Mettez ici le traitement de @rc : vous pouvez par exemple vous envoyer un e-mail lorsque la trace vient d'être créée,
-- à l'aide de la procédure msdb.dbo.sp_send_db_mail
DECLARE @on BIT; SET @on = 1;
EXEC sp_trace_setevent @TraceID, 13, 12, @on; -- Event : SQL:BatchStarting, SPID column (Mandatory)
EXEC sp_trace_setevent @TraceID, 13, 64, @on; -- Event : SQL:BatchStarting, SessionLoginName
EXEC sp_trace_setevent @TraceID, 13, 1, @on; -- Event : SQL:BatchStarting, TextData
EXEC sp_trace_setevent @TraceID, 13, 14, @on; -- Event : SQL:BatchStarting, StartTime
EXEC sp_trace_setevent @TraceID, 13, 35, @on; -- Event : SQL:BatchStarting, DatabaseName
-- Ajout des filtres
/*
sp_trace_setfilter myTrace,
myColumn (64 = SessionLoginName),
myLogicalOperator (AND : 0, OR : 1),
myComparisonOperator (6 = LIKE),
myComparisonValue
*/
EXEC sp_trace_setfilter @TraceID, 64, 1, 6, N'nomDeLogin'
-- Démarrage de la trace
EXEC sp_trace_setstatus @TraceID, 1;
END;
GO |
Partager