Customized profiler trace stops running
-
Hello,
I have a question regarding a profiler trace which suddenly stops after a while.
Are there any known issues in 2008 or 2005 when creating a customized profiler trace via the command line ( not gui ) that it just haltsafte a certain amount of time ?
i.e
EXEC sp_trace_setstatus 2, 1
GO
DECLARE @on BIT
SELECT @on = 1
exec sp_trace_setevent 2, 14, 6, @on
exec sp_trace_setevent 2, 14, 7, @on
exec sp_trace_setevent 2, 14, 8, @on
exec sp_trace_setevent 2, 14, 9, @on
exec sp_trace_setevent 2, 14, 10, @on
............
ty
-
Offhand, these are the reasons I remember for a trace stop being stopped
1.the SQL server is stopped and started. If that is the case, you might need to create a procedure that creates your trace, and use the proc option to make it a start up proc, so every reboot/restart also restarts your trace.
2. You explicitly included a stop time in your sp_trace_create command
3. You defined a max file size, no rollover, and the trace file hits it's maximum size.
4. someone actually issued the exec sp_trace_setstatus @TRACEID, 0 ---stop trace, you must know the traceid to stop it
--#################################################################################################--create the traceexec sp_trace_create @traceid = @traceidout output,@options = @myoptions, --i.e. set @myoptions = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE@tracefile = @mypath,@maxfilesize = @mymaxfilesize,@stoptime = @mystoptime,@filecount = @mymaxRolloverFiles--#################################################################################################Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Thanks again for responding...
From what I was told from a colleague of mine, the items mentionned are indeed good points but I am confirming that the last 3 have definitely been been ruled out,
and the start / stop sql server may have been the cause but not for all occurrences of the trace halting in the background.
Therefore, what I would like to implement is a check status scripts and restart if halted along with that server side restart proc.
SELECT * FROM ::fn_trace_getinfo(NULL)
SELECT * FROM ::fn_trace_geteventinfo(2)
Since I'm not the coder , I would need help with this
-
if you've ruled out the last 3, the only way I know of (and it's probably this, i guess) is it's getting stopped because the server stops and starts.
instead of a command line, you will have to make a stored procedure with the trace definition.
here's an example of one I've posted here several times, but haven't alterd in at least a year:
then, after that has been installed in the master database, you are need to mark it as a start up procedure:
execute sp_procoption@ProcName = 'sp_AddMyTrace' ,@OptionName = 'startup',@OptionValue = 'true'you can easily see if the stop/start thing is the cause:
select the highest date value from your trace file, then look in the SQL log and see if the service stopped and restarted;
you might see it's happening at a consistent time too, like 2am or something, so you know if it's some outside process doing the stopping (virus scanner? Operating System Defrag script?)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for that !
I will review and see what I can do and get back to you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply