January 30, 2007 at 2:35 pm
What would be the best way to set up a daily trace that stops and restars every day. I need to have a seperate trace file for each day. Any help you could give would be great.
January 30, 2007 at 2:59 pm
Schedule a SQL job that executes every day
sp_trace_create which has a stop time parameter. Look in Books online for syntax (books online comes with SQL server standard install)
January 30, 2007 at 7:30 pm
Do you know where I can find a sample of a trace script. What i am after is to run this as a job via xp_cmdshell which would execute the trace script at let say 12:01AM and the execute a trace script that stops the running trace at say 11:55Pm. The the Create script would run again creating another trace fie for the next day.
January 30, 2007 at 10:07 pm
Type in sp_trace_create on the index tab of books online. That will give you the syntax.
If you do not have access let me know.
Just wondering. You are using xp_cmdshell and scheduling it via MS scheduler? Why not just run as a cmd line in a sql server job?
January 30, 2007 at 10:43 pm
You schedule the trace start script in schedule job and pass trace stop date dynamically...
and you use the following script to stop the trace...in different job step or job...
declare @traceid int
SELECT @traceid = traceid FROM :: fn_trace_getinfo(default)
where property =2 and convert(Varchar(100),value) like '%TraceFilename%'
exec sp_trace_setstatus @traceid = @traceid, @status = 0
exec sp_trace_setstatus @traceid = @traceid, @status = 2
MohammedU
Microsoft SQL Server MVP
January 31, 2007 at 5:49 am
Thanks for all the input. I ended up using profiler to create a trace template script. Then incorporating it into a stored procedure which passed in the number of minutes you want it to run.
Edited the script to tack on datetime information for the filename.
Ran the sp in a job presetting the parameters.
Again thank you for all your input.
January 31, 2007 at 6:55 am
Can you post the final script you used? That'll help someone else who stumbles on that thread in the future.
January 31, 2007 at 8:01 am
Sure, here it is.
CREATE PROCEDURE usp_DailyQMSTrace
@sec int = 600, --Duration in seconds for the trace
@TraceID INT OUTPUT
AS
-- Create a Queue
declare @rc int
declare @QMSTraceID int
declare @maxfilesize bigint
declare @FileName nVarchar(256)
Declare @DateTime datetime
Declare @StartDate datetime --When Trace started
Declare @CurrentTime Datetime
Set @CurrentTime = current_timestamp
SELECT @StartDate = GETDATE()
SELECT @FileName = N'\\WSContractor01\C$\Work\QMSTrace-'
Set @FileName = @filename + (select convert(varchar,current_timestamp,112))
set @maxfilesize = 50
exec @rc = sp_trace_create @TraceID output, 2, @FileName, @maxfilesize, @DateTime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply