April 21, 2011 at 7:38 am
Dear All,
We have SQL Server 2005 standard installed in one of the server at remote location.
I want to capture the transactions happening at 1AM till 1.30 AM.
Is there any way to start the profiler automaticaly at 1 AM and stop at 1.30 AM.?
Thanks in advance.
April 21, 2011 at 8:50 am
Create your trace using the profiler GUI, then Export your trace definition to a file so you have all the SQL commands to create the trace.
Modify the script to set your trace file name (you should see the comments telling you where).
At the bottom of the script where you see "exec sp_trace_setstatus @TraceID, 1", add the following lines...
WAITFOR DELAY '00:30:00' --wait for 30 mins or however long you want
exec sp_trace_setstatus @TraceID, 0 --stop the trace
exec sp_trace_setstatus @TraceID, 2 --close trace and delete definition
Now set up a SQL Agent job that runs the exported SQL commands, and schedule it to start at 1am
April 21, 2011 at 8:53 am
Thank you somuch. Will try that way.
April 22, 2011 at 2:45 am
Hi,
I missed to add those additional lines you mentioned. Hence whatever .trc file created is still there in the folder. Actualy I want to remove those files now. When I try to delete it gives the error saying "..it's used by some other program." What should I do to delete those unwanted .trc file now.?
now I have added the following 3 lines in the script.
WAITFOR DELAY '00:65:00'
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
Kindly help. Thanks again.
April 22, 2011 at 3:48 am
That probably means your trace is still running.
I don't have access to SQL Server at the moment to give definitive instructions, but you need to find out the id of the running trace and stop it. Check in books online, but I think you can use fn_trace_getinfo(0) to find the Id of the trace, then use the previous intructions to stop and remove the trace...
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
April 22, 2011 at 4:56 am
Thanks for quick response. I created a new trace file after adding the commands to close it. Even that file I am not able to delete. Getting the same error. Anyway, will try what u have mentioned. Thanks again.
By the way, I had mentioned the start & end times as well.
April 22, 2011 at 5:08 am
Wel, I got the ID from sys.traces and manualy executed it.
Now am able to delete those unwanted file.
Just one more thing, what's the script to "ENABLE FILE ROLLOVER" ?
Yesterday I dint get sufficient data with the default script. Got only 5 MB.
Thanks.
April 24, 2011 at 7:09 am
Here it is.
sp_trace_create @TraceID output, 2, @path, @maxfilesize, @Datetime
the second parameter in the "sp_trace_create" must be 2 to enable rollover.
April 25, 2011 at 4:45 am
Thanks Santhosh.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply