Running profiler non-stop
-
Hi!
I have a server, where a number of traces are run. I want to run them all the time, but when I have to restart the server (updates, service packs, patches, etc) I have to set up all traces all over again. Is there a way to automatically start all prepared traces when server starts?
And another question: each time I stop a trace the output redirection to a table is reset. If I try to set up saving output to the same table, the program asks me to overwrite existing table. Is there a way to save the trace results to the same table without previous data loss?
(I am using MS SQL 2000 sp3 + Windows 2000 Server sp4)
Thanks.
-
Profiler is merely the GUI front-end to a server side trace. You can write those yourself (see BOL-sp_trace_create or script out a trace from Profiler). I've written several and if you are selective about the information you gather, there's no real performance hit on your server. (I don't let on but I have a trace running all the time that logs any query over x CPU duration. The users are mystified when I call them on bad queries - they think I'm constantly hovering over a performance monitor screen... <g>
There may be different approaches to gathering the data and I'm expecting to get flamed/bashed/etc. for suggesting this, but I'd redirect the output to a file and set the traces to toggle off/on every hour or so. This releases the hook on the output file and allows you to pull in the data to a table. The reason I like this approach is because you can pull the file into a temp table, then select out only the data you want to look at and store it in a permanent table. If you decide to go this route, make sure you follow this logic sequence: start the trace to a file, stop the trace (after a period of time), pull the data into a temp table/process/etc., rename the trace file, restart the trace. Rinse and repeat, so the speak <g>. This also leaves a trail of breadcrumbs (the renamed files) in the event you want to research something else. Beware: only log stuff you're interested in or these files will get big. I personally chose to rename the files with a current timestamp.
Cheers,
Ken
-
Here's an example to get you started. You can drop this in master if you like, then execute it. Open a second query window up and pummel pubs (the trace only runs for 60 seconds in this example, so pummel fast). If you're planning on running a trace for a longer period of time, carve this example into two parts: the start and the end.
Cheers,
Ken
PS Apologies for any unplanned smilies and whacked wordwrap...
----------[cut here]----------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
IF EXISTS (
SELECT name
FROM dbo.sysobjects
WHERE id = OBJECT_ID('sys_TracePI')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE sys_TracePI
GO
CREATE PROCEDURE sys_TracePI AS
/*
** Procedure Name.......: sys_TracePI
**
** Creation Date........: 5/19/2004
** Author...............: Ken Klaft
** Purpose..............: Investigate activity using server-side trace
** About that name......: Trace File Private Investigator
**
** Modification History
** ====================
** Date Init Description
** ========== ==== ===========
** 5/19/2004 KTK Procedure created.
**
** Note: Do NOT include the suffix for the trace file!
**
*/
SET NOCOUNT ON
DECLARE @FileLocation NVARCHAR(1000), -- Trace file location
@rc INT, -- Return code
@TraceID INT, -- Trace ID
@MaxTraceSize BIGINT, -- Maximum file size (in megabytes) before rollover
@Duration DATETIME, -- Duration of trace
@On BIT, -- Bit variable for setting trace events (1=ON)
@StartTime DATETIME, -- Investigation start time
@EndTime DATETIME, -- Investigation termination time
@DBID INT -- DBID of the database to focus on
SELECT @FileLocation = 'C:\Program Files\Microsoft SQL Server\',
@MaxTraceSize = 500,
@Duration = DATEADD(mi, 1, GETDATE()),
@On = 1,
@StartTime = GETDATE(),
@DBID = DB_ID('pubs')
SELECT @FileLocation = @FileLocation +
CONVERT(VARCHAR(4),DATEPART(yyyy, GETDATE()))+
CASE
WHEN DATEPART(mm, GETDATE()) < 10 THEN '0'+CONVERT(VARCHAR(2),DATEPART(mm, GETDATE()))
ELSE CONVERT(VARCHAR(2),DATEPART(mm, GETDATE()))
END +
CASE
WHEN DATEPART(dd, GETDATE()) < 10 THEN '0'+CONVERT(VARCHAR(2),DATEPART(dd, GETDATE()))
ELSE CONVERT(VARCHAR(2),DATEPART(dd, GETDATE()))
END +
CASE
WHEN DATEPART(hh, GETDATE()) < 10 THEN '0'+CONVERT(VARCHAR(2),DATEPART(hh, GETDATE()))
ELSE CONVERT(VARCHAR(2),DATEPART(hh, GETDATE()))
END +
CASE
WHEN DATEPART(mi, GETDATE()) < 10 THEN '0'+CONVERT(VARCHAR(2),DATEPART(mi, GETDATE()))
ELSE CONVERT(VARCHAR(2),DATEPART(mi, GETDATE()))
END
/*
** Housekeeping
*/
-- Create a Queue
EXEC @rc = sp_trace_create @TraceID OUTPUT, @options=2, @tracefile=@FileLocation, @maxfilesize=@MaxTraceSize, @stoptime=@Duration
IF (@RC != 0)
BEGIN
PRINT CASE @rc
WHEN 1 THEN 'Unknown error.'
WHEN 10 THEN 'Invalid options.'
WHEN 12 THEN 'File not created.'
WHEN 13 THEN 'Out of memory.'
WHEN 14 THEN 'Invalid stop time.'
WHEN 15 THEN 'Invalid parameters.'
ELSE LTRIM(STR(@RC))+' code not defined.'
END
RETURN(0)
END
-- Set the events
EXEC sp_trace_setevent @TraceID, 12, 1, @On -- TextData
EXEC sp_trace_setevent @TraceID, 12, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 12, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 12, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 12, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 12, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 12, 11, @On -- SQLLoginName
EXEC sp_trace_setevent @TraceID, 12, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 12, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 12, 16, @On -- Reads
EXEC sp_trace_setevent @TraceID, 12, 17, @On -- Writes
EXEC sp_trace_setevent @TraceID, 12, 18, @On -- CPU
-- Set the Filters
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, @DBID -- Database ID
--EXEC sp_trace_setfilter @TraceID, 18, 0, 4, 2 -- CPU >= 2
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1 -- Start trace
-- Wait for trace to finish
WAITFOR TIME @Duration -- Wait for trace to stop automatically
-- Give the trace file time to close
WAITFOR DELAY '000:01:00' -- Wait for the OS hook on the file to release
-- Pull in trace data
SELECT @FileLocation = @FileLocation+'.trc'
SELECT IDENTITY(INT,1,1) AS RowNumber, *
INTO #trace
FROM ::fn_trace_gettable(@FileLocation,DEFAULT)
CREATE TABLE #TraceInfo (
RowNumber INT,
QueryText VARCHAR(7000) NULL,
HostName VARCHAR(30),
LoginName VARCHAR(30),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
Related SMALLINT
)
-- Move TextData data to QueryText column
INSERT INTO #TraceInfo (RowNumber, QueryText, HostName, LoginName, Duration, Reads, Writes, CPU, Related)
SELECT RowNumber, CONVERT(VARCHAR(7000),TextData), HostName, LoginName, CONVERT(INT,Duration), CONVERT(INT,Reads), CONVERT(INT,Writes), CONVERT(INT,CPU), 0
FROM #trace
DROP TABLE #trace
/*
** Clean up trace data
*/
-- Remove unwanted records
DELETE
FROM #TraceInfo
WHERE QueryText LIKE '%sqlagent%'
DELETE
FROM #TraceInfo
WHERE QueryText LIKE 'Testing connection'
DELETE
FROM #TraceInfo
WHERE Duration = 0
AND Reads = 0
AND Writes = 0
AND CPU = 0
-- Replace all CR/LFs with spaces
UPDATE #TraceInfo
SET QueryText = REPLACE(QueryText, CHAR(10),' ')
UPDATE #TraceInfo
SET QueryText = REPLACE(QueryText, CHAR(13),' ')
UPDATE #TraceInfo
SET QueryText = REPLACE(QueryText, ' ',' ')
/*
** Output
*/
PRINT 'Output trace file : '+@FileLocation
PRINT 'Commencement : '+CONVERT(VARCHAR(50),@StartTime)
PRINT 'Termination : '+CONVERT(VARCHAR(50),GETDATE())
PRINT ' '
SELECT QueryText FROM #TraceInfo
DROP TABLE #TraceInfo
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply