Customized profiler trace stops running

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Customized profiler trace stops running

Customized profiler trace stops running

Rate Topic

Display Mode

Topic Options

Author

Message

johnnyrmtl

Posted Thursday, March 21, 2013 12:51 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

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

Post #1433978

Lowell

Posted Thursday, March 21, 2013 12:59 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1433986

johnnyrmtl

Posted Thursday, March 21, 2013 1:18 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

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 :)

Post #1433996

Lowell

Posted Thursday, March 21, 2013 1:43 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1434011

johnnyrmtl

Posted Friday, March 22, 2013 7:44 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

Thanks for that !

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

Post #1434273

« Prev Topic | Next Topic »

Permissions