Customized profiler trace stops running

Question

Customized profiler trace stops running

johnnyrmtl

Hall of Fame

Points: 3505
More actions
March 21, 2013 at 12:51 pm

#275417

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 trace

exec 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!

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 2

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 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

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:

sp_AddMyTrace_Latest.txt

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!

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 4

Thanks for that !

I will review and see what I can do and get back to you.