/* EXEC PerfDB.dbo.TraceExecutionsDelete EXEC PerfDB.dbo.TraceExecutionsStart @Minutes = 60, @Path = N'B:\Traces\BaseLine' EXEC PerfDB.dbo.TraceExecutionsLoad @Path = N'B:\Traces\BaseLine.trc' EXEC PerfDB.dbo.TraceExecutionsManipulateData @Rows = 200 EXEC PerfDB.dbo.TraceExecutionsReport @StartDate = '20090125', @EndDate = '20090127 23:59', @Rows = 200 --DELETE FROM [BaseLine] WHERE InsertTime BETWEEN '20090928' AND '20090930' */ USE PerfDB; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TraceExecutionsStart]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[TraceExecutionsStart] ; GO CREATE PROCEDURE TraceExecutionsStart ( @Database sysname = NULL ,@MaxFileSize bigint = 50 ,@FileCount int = 50 ,@Minutes int = 60 ,@Path sysname ) /* Yaniv Etrogi http://www.sqlserverutilities.com Define the trace and start it. */ AS SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- EXEC PerfDB.dbo.TraceExecutionsStart @Minutes = 60, @Path = N'B:\Traces\BaseLine', @Database = N'IR' DECLARE @RC int, @TraceID int, @EventClass int, @On bit, @StopTime datetime; SELECT @On = 1, @StopTime = DATEADD(minute, @Minutes, CURRENT_TIMESTAMP ); EXEC @RC = sp_trace_create @TraceID OUTPUT ,@options = 2 -- file rollover ,@tracefile = @Path ,@maxfilesize = @MaxFileSize -- MB ,@stoptime = @StopTime ,@filecount = @FileCount; IF (@RC <> 0) OR (@@ERROR <> 0) BEGIN; SELECT @RC; RETURN; END ; -- SP:Completed. Indicates when the stored procedure has completed. SELECT @EventClass = 43 ; EXEC sp_trace_setevent @TraceID, @EventClass, 34, @On -- ObjectName EXEC sp_trace_setevent @TraceID, @EventClass, 10, @On -- ApplicationName EXEC sp_trace_setevent @TraceID, @EventClass, 35, @On -- DatabaseName EXEC sp_trace_setevent @TraceID, @EventClass, 8, @On -- HostName EXEC sp_trace_setevent @TraceID, @EventClass, 16, @On -- Reads EXEC sp_trace_setevent @TraceID, @EventClass, 17, @On -- Writes EXEC sp_trace_setevent @TraceID, @EventClass, 18, @On -- CPU EXEC sp_trace_setevent @TraceID, @EventClass, 13, @On -- Duration EXEC sp_trace_setevent @TraceID, @EventClass, 48, @On -- RowCounts EXEC sp_trace_setevent @TraceID, @EventClass, 12, @On -- SPID EXEC sp_trace_setevent @TraceID, @EventClass, 14, @On -- StartTime EXEC sp_trace_setevent @TraceID, @EventClass, 11, @On -- LoginName -- RPC:Completed. Occurs when a remote procedure call (RPC) has completed. SELECT @EventClass = 10 ; EXEC sp_trace_setevent @TraceID, @EventClass, 34, @On -- ObjectName EXEC sp_trace_setevent @TraceID, @EventClass, 10, @On -- ApplicationName EXEC sp_trace_setevent @TraceID, @EventClass, 35, @On -- DatabaseName EXEC sp_trace_setevent @TraceID, @EventClass, 8, @On -- HostName EXEC sp_trace_setevent @TraceID, @EventClass, 16, @On -- Reads EXEC sp_trace_setevent @TraceID, @EventClass, 17, @On -- Writes EXEC sp_trace_setevent @TraceID, @EventClass, 18, @On -- CPU EXEC sp_trace_setevent @TraceID, @EventClass, 13, @On -- Duration EXEC sp_trace_setevent @TraceID, @EventClass, 48, @On -- RowCounts EXEC sp_trace_setevent @TraceID, @EventClass, 12, @On -- SPID EXEC sp_trace_setevent @TraceID, @EventClass, 14, @On -- StartTime EXEC sp_trace_setevent @TraceID, @EventClass, 11, @On -- LoginName -- SQL:BatchCompleted. Occurs when a Transact-SQL batch has completed. SELECT @EventClass = 12 ; EXEC sp_trace_setevent @TraceID, @EventClass, 1, @On -- TextData EXEC sp_trace_setevent @TraceID, @EventClass, 34, @On -- ObjectName EXEC sp_trace_setevent @TraceID, @EventClass, 10, @On -- ApplicationName EXEC sp_trace_setevent @TraceID, @EventClass, 35, @On -- DatabaseName EXEC sp_trace_setevent @TraceID, @EventClass, 8, @On -- HostName EXEC sp_trace_setevent @TraceID, @EventClass, 16, @On -- Reads EXEC sp_trace_setevent @TraceID, @EventClass, 17, @On -- Writes EXEC sp_trace_setevent @TraceID, @EventClass, 18, @On -- CPU EXEC sp_trace_setevent @TraceID, @EventClass, 13, @On -- Duration EXEC sp_trace_setevent @TraceID, @EventClass, 48, @On -- RowCounts EXEC sp_trace_setevent @TraceID, @EventClass, 12, @On -- SPID EXEC sp_trace_setevent @TraceID, @EventClass, 14, @On -- StartTime EXEC sp_trace_setevent @TraceID, @EventClass, 11, @On -- LoginName /* sp_trace_setfilter @TraceID, @columnid, @logical_operator, @comparison_operator, @value @logical_operator: AND (0) or OR (1) @comparison_operator: 0 = Equal, 1 = Not equal, 2 = Greater than, 3 = Less than, 4 = Greater than or equal, 5 = Less than or equal, , 6 = Like, 7 = Not like */ -- Exclude TextData --EXEC sp_trace_setfilter @TraceID, 1, 0, 7, N'EXEC sp_reset_connection'; -- Include specific Database IF @Database IS NOT NULL BEGIN; EXEC sp_trace_setfilter @TraceID, 35, 0, 0, @Database; END; SELECT TraceID = @TraceID; -- Start trace EXEC sp_trace_setstatus @TraceID, 1; ------------------------------------------------------------ /* -- Controll trace status EXEC sp_trace_setstatus @traceid = 3, @status = 1 --start --<---- Edit TraceId EXEC sp_trace_setstatus @traceid = 3, @status = 0 --stop EXEC sp_trace_setstatus @traceid = 3, @status = 2 --remove -- See all traces defined at the server SELECT [id], [status], [path], event_count, dropped_event_count, max_size, max_files, is_rollover, is_shutdown, buffer_count, buffer_size, file_position, start_time, last_event_time FROM sys.traces --WHERE Id = 1 -- Events SELECT e.name AS Event_Name, c.name AS Column_Name FROM fn_trace_geteventinfo(3) ei --<---- Edit TraceId JOIN sys.trace_events e ON ei.eventid = e.trace_event_id JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id; -- Filters SELECT columnid ,c.name AS Column_Name ,logical_operator ,comparison_operator ,value FROM fn_trace_getfilterinfo(3) ei --<---- Edit TraceId JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id; -- Event / Columns possible combination SELECT e.name AS [event] ,c.name AS [column] FROM sys.trace_event_bindings b INNER JOIN sys.trace_events e ON e.trace_event_id = b.trace_event_id INNER JOIN sys.trace_columns c ON c.trace_column_id = b.trace_column_id ORDER BY e.name; -- SubClass values SELECT c.name AS [column] ,e.name AS [event] --,c.type ,s.subclass_value ,s.subclass_name FROM sys.trace_columns c INNER JOIN sys.trace_subclass_values s ON c.trace_column_id = s.trace_column_id INNER JOIN sys.trace_events e ON e.trace_event_id = s.trace_event_id WHERE 1 = 1 AND e.name LIKE 'Audit Login'; SELECT * INTO PerfDB.dbo.[BaseLineFull] FROM ::fn_trace_gettable(N'b:\Traces\BaseLine.trc', DEFAULT); */