Way of Scheduling SQL Profiler

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Way of Scheduling SQL Profiler

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-04-27 : 04:33:52

Dear All,
Is there any way to schedule the SQL Server Profiler to trace the SQL Transaction for 10 mins at certain moment of the day?

Thanks.

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-27 : 08:46:53

I've included some code I used a while ago below. I use 1 job to start the trace and pass it the file name I want it to store the results in. You can create the Sp and kick it off with a job.
The second piece is an sp I used to stop the job which would run x minutes after the start job so you can shut the trace down. In my situation, I do a sample once per hour for 10 mins and suck the file into a table.

--Code to start a trace from sp

CREATE procedure trace_start
@filename varchar(8000)
as

--Set the time for the trace to stop
declare @dt_end datetime
select @dt_end = convert(varchar(30), getdate(), 101)+' 23:59:00'

--Create an output variable for the id of the trace
DECLARE @TraceID int

EXEC CreateTrace
@filename,
@OutputTraceID = @TraceID OUT,
@OverwriteFile=1,
@StopAt=@dt_end

EXEC AddEvent
@TraceID,
'SP:Completed',
'TextData, Duration,StartTime,EndTime,ApplicationName,DatabaseName,DatabaseID,NTUserName,SPID,SQLSecurityLoginName,CPU,READS,WRITES,'

EXEC AddFilter
@TraceID,
'Duration',
0,--Duration of the
'>='

EXEC AddFilter
@TraceID,
'SQLSecurityLoginName',
'srCXPTest',--Duration of the
'NOT LIKE'

EXEC AddFilter
@TraceID,
'NTUserName',
'srCXPTest',--Duration of the
'NOT LIKE'

EXEC AddFilter
@TraceID,
'ApplicationName',
'MS SQLEM',--Duration of the
'NOT LIKE'

EXEC AddFilter
@TraceID,
'TextData',
'p_getNextClarifyAPIRequest',--Duration of the
'NOT LIKE'

--exec p_getNextClarifyAPIRequest

--MS SQLEM

--EXEC AddFilter
--@TraceID,
--'DatabaseID',
--6,--Duration of the
--'='

EXEC StartTrace @TraceID
GO

--CODE TO STOP A TRACE

CREATE PROC dbo.StopTrace
(
@TraceID int
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @ReturnValue int, @Stop int
DECLARE @ProcedureName varchar(25), @Error varchar(100)
DECLARE @TraceErrors table (Error int, [Description] varchar(100))

SET @ProcedureName = 'StopTrace'
SET @Stop = 0

IF NOT EXISTS
(
SELECT 1
FROM ::fn_trace_getinfo(@TraceID)
)
BEGIN
/*RAISERROR('Cannot find trace with ID %d. Source: %s', 16, 1, @TraceID, @ProcedureName)
RETURN -1*/
RETURN
END

INSERT INTO @TraceErrors (Error, [Description]) VALUES (1, 'Unknown error')
INSERT INTO @TraceErrors (Error, [Description]) VALUES (8, 'The specified Status is not valid')
INSERT INTO @TraceErrors (Error, [Description]) VALUES (9, 'The specified Trace Handle is not valid')
INSERT INTO @TraceErrors (Error, [Description]) VALUES (13, 'Out of memory')

EXEC @ReturnValue = sp_trace_setstatus @traceid = @TraceID, @status = @Stop

IF @ReturnValue <> 0
BEGIN
SET @Error = (SELECT [Description] FROM @TraceErrors WHERE Error = @ReturnValue)
SET @Error = COALESCE(@Error, 'Unknown error ' + CAST(@ReturnValue AS varchar(10)))
RAISERROR('Failed to stop trace. Error: %s. Source: %s', 16, 1, @Error, @ProcedureName)
RETURN -1
END

RETURN 0

END
GO

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources