Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Customized profiler trace stops running Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 12:51 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:11 AM
Points: 75, Visits: 340
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
Posted Thursday, March 21, 2013 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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
Posted Thursday, March 21, 2013 1:18 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:11 AM
Points: 75, Visits: 340
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
Posted Thursday, March 21, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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
Posted Friday, March 22, 2013 7:44 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:11 AM
Points: 75, Visits: 340
Thanks for that !

I will review and see what I can do and get back to you.
Post #1434273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse