October 29, 2008 at 9:22 am
Im properly going about this in a backward fashion and was interested to see if theres a more elegant solution ...
I script out the TSQL_SP trace from profiler and use it to create a proc which I then call via a scheduled job.
I run a seperate job that contains
declare @traceid int
SELECT @traceid = traceid
FROM :: fn_trace_getinfo(default)
where value = 'c:\Trace_Files\TSQL_SP_LOCAL'
EXEC sp_trace_setstatus @traceid, 0 -- Stops Trace
EXEC sp_trace_setstatus @traceid, 2 -- Closes Trace
I can then schedule it to run for two hours and the second job to kick in and kill it leaving the trace file for analysis later.
Like I said .. is there a better way to do this ?
Also it fails if the trace file already exists .. no big deal but if one day I forget to delete it
~simon
October 29, 2008 at 9:37 am
You can set an end time when you create the trace. It's an optional parameter to the create trace proc.
What I typically do with the files is add the date to the end of it so I can see easily when the trace was created, and also I don't get the file exists problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 9:45 am
sp_trace_create has the @stoptime parameter that you can set. When you start a trace that has the stop time set the trace is stopped and removed when the stop time is reached.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 29, 2008 at 10:07 am
thank you both .. my only problem with that is the lack of start time parameter (please correct me if Im wrong )
Gail did you change the file name by wrapping it in dynamic sql Im guessing
~simon
October 29, 2008 at 10:08 am
Watch disk space if you have these regularly scheduled.
October 29, 2008 at 10:14 am
Correct there is a lack of a start time so you would need to continue to start the trace the same way you are doing it now, but you could use the stop time parameter to stop and clear it instead of having the second job. In your sp you could set the stop time dynamically using getdate().
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 29, 2008 at 2:06 pm
Simon_L (10/29/2008)
Gail did you change the file name by wrapping it in dynamic sql Im guessing
No. No need for that at all.
I put code like the following into a job and schedule that job for the time I want the trace to start. The job runs, the trace starts and it will automatically end 2 hours later
declare @TraceID int
declare @maxfilesize bigint
declare @filename varchar(100)
declare @endtime datetime
set @maxfilesize = 250
set @filename = 'Mytrace' + replace(convert(varchar(10), getdate(),121),'-','')
set @endtime = DATEADD(hh,2,getdate())
exec sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @endtime
-- set events and filter here
exec sp_trace_setstatus @TraceID, 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 4:09 am
thank you both for your advice... appreciated 🙂
I shall go have a play and see how it works
~si
October 30, 2008 at 5:36 am
Hi
Gail .. made a couple of changes to your code and it works wonderfully thank you
Jack start in it in a job and let the end time stop it .. again thank you
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @filename nvarchar(128)
declare @endtime datetime
set @maxfilesize = 250
set @filename = 'c:\Trace_Files\Mytrace' + convert(varchar(8), getdate(), 112) + '_' +replace(substring(convert(varchar(20), getdate(), 113), 13, 8), ':', '-')
set @endtime = DATEADD(n,1,getdate())
exec sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @endtime
-- Set the events
I made the filename variable a nvarchar as it didnt seem to like varchar being passed in, added a location to the file to stop it ending up in system32 and a time to the filename so it can be ran multiple times
best wishes
~si
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply