July 25, 2008 at 6:27 am
I can see only Enable trace stop time in gui of profiler.
i need to run the profiler in midnight 12 to 6 in the morning. in this case i can only schedule it for the stop time not for the start time?
any wayout please....
Cheers,
DKG
July 28, 2008 at 11:36 am
You can start a trace without using the Profiler GUI. See my replies in this thread describing how to create, start and stop a server-side trace: http://qa.sqlservercentral.com/Forums/Topic529790-5-1.aspx
Greg
July 28, 2008 at 1:59 pm
Greg, thanks for your input on this.
I have gone through the article and long discussion with in the article.
Seems useful, the script mentioned in the article is the output of Profiler->File->Export->Script Trace Definition
but when i ran it within query analyzer to test whether it works fine or not it failed with error
The trace file name is not valid
Now which file and location needs to be specified for trace location. Even I’ve tested to create a physical location e.g. D:\test\test1.trc
When I specified this, even its not working....Could you clear my confusion?
Please also let me know by example how to stop the trace on specified date and time.
Cheers,
DKG
July 28, 2008 at 2:33 pm
When you open the script you should reset the file location, you can use a file share. You can define the stop time as the 5th parameter to sp_trace_create.
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
July 28, 2008 at 2:45 pm
Jack,
this peice of code contains sp_trace_create and the file name:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
Now please let me know if i want to stat it at '28/Jul/08 23:00' and want to stop at '29/Jul/08 05:00' how the statement would be and my other confusion is for file name, its now working by any way. Please make it clear to me.
I have specified the physical location like d:\test\test.trc, now this test.trc should exists or not if exist how this would be created?
Thanks,
DKG
July 28, 2008 at 2:45 pm
The trace file name is nvarchar(245). Did you specify N'D:\test\test1.trc? You can set the stop time using the @stoptime parameter of sp_trace_create or you can use sp_trace_setstatus to stop it.
Greg
July 28, 2008 at 2:50 pm
Greg,
yes i have specified exec @rc = sp_trace_create @TraceID output, 0, N'C:\trace_dev_test.trc', @maxfilesize, NULL
if (@rc != 0) goto error
where trace_dev_test.trc file exists.
in first run it gives trace id 3 as a result and in second run it gives
Msg 19067, Level 16, State 1, Procedure sp_trace_create, Line 1
Cannot create a new trace because the trace file path is found in the existing traces.
error.
July 28, 2008 at 2:51 pm
You only need to create the trace once.
Once you create the trace, put sp_trace_setstatus trace_id, 1 in a job sceduled to run at 11:00 PM and put sp_trace_setstatus trace_id, 0 in a job scheduled to run at 5:00 AM.
The output file won't be created until the trace starts.
Greg
July 28, 2008 at 3:01 pm
Ok, means all i need to create a trace file on some specific location and two sql agent jobs one would have sp_trace_setstatus trace_id, 1 - job having schedule to run at 11:00 PM
Other job would have sp_trace_setstatus trace_id, 0 - job having schedule to run at 5:00 AM next day.
Please let me know if still i am missing something 🙂
July 28, 2008 at 3:10 pm
Looks right to me. BTW, the 2 option is sp_trace_create specifies that a new trace file will be created when the current file reaches it's max file size (which you can also specify), so if there's a lot of activity, don't be surprised to find several trace files.
Greg
July 28, 2008 at 3:13 pm
Looks good to me. By doing it this way the trace definition remains available and you can run it whenever you need.
You can check the traces that are defined by querying sys.traces.
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
July 28, 2008 at 3:45 pm
Thanks Greg and Jack for your time and valuable suggestion it worked this time. Trace started automatically.
But I think ive done something wrong with the trace stop job. what i did i have copied the same code in the stop job with replacement of sp_trace_setstatus trace_id, 0
It failed at stop time with error ::
Executed as user: NT AUTHORITY\SYSTEM. Cannot create a new trace because the trace file path is found in the existing traces. [SQLSTATE 42000] (Error 19067). The step failed.
July 28, 2008 at 5:21 pm
Your stop job should only have sp_trace_setstatus in it, not the sp_trace_create part. If you run that again it's like you're trying to create the same trace again.
Greg
July 29, 2008 at 1:08 am
Greg,
This time ive supplied
exec sp_trace_setstatus 0 or tried with exec sp_trace_setstatus '0' also
in the stop job, now it says
Message
Executed as user: NT AUTHORITY\SYSTEM. Procedure or Function 'sp_trace_setstatus' expects parameter '@status', which was not supplied. [SQLSTATE 42000] (Error 201). The step failed.
July 30, 2008 at 1:22 pm
Greg/Jack,
Your help would be appriciated...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply