SQl Profiler not writing to output file

• Rachel Lee-244397

  SSC Veteran

  Points: 234

  More actions

  May 8, 2006 at 7:32 pm

  #366105

  I have a trace running that should be writing to a file, but the file size is zero, even after stopping the trace.

  When I delete the trace, the output file then has a value for the size and does contain all the auditing I expect to see. Does anyone know why the trace has to be dropped before I can see what is in the output??

• raj-247939

  SSCertifiable

  Points: 5999

  More actions

  May 10, 2006 at 10:48 pm

  #637151

  Pls provide the trace name u are executing.

   

  from

  Kiler

• Rachel Lee-244397

  SSC Veteran

  Points: 234

  More actions

  May 14, 2006 at 3:05 pm

  #637753

  This is what I am running:

  EXEC sp_trace_create 1 OUTPUT, 6, C:\Logs\SQLServer_Audit', 10;

  EXEC sp_trace_setevent @traceid, 102, 1, @on;

  EXEC sp_trace_setevent @traceid, 102, 10, @on;

  EXEC sp_trace_setevent @traceid, 102, 11, @on;

  EXEC sp_trace_setevent @traceid, 102, 14, @on;

  EXEC sp_trace_setevent @traceid, 102, 15, @on;

  EXEC sp_trace_setevent @traceid, 102, 34, @on;

  EXEC sp_trace_setevent @traceid, 102, 35, @on;

  EXEC sp_trace_setevent @traceid, 102, 37, @on;

  EXEC sp_trace_setevent @traceid, 102, 38, @on;

  EXEC sp_trace_setevent @traceid, 102, 39, @on;

  EXEC sp_trace_setevent @traceid, 102, 40, @on;

  EXEC sp_trace_setevent @traceid, 102, 42, @on;

  EXEC sp_trace_setevent @traceid, 102, 6, @on;

  EXEC sp_trace_setevent @traceid, 102, 7, @on;

  EXEC sp_trace_setevent @traceid, 102, 8, @on;

  EXEC sp_trace_setevent @traceid, 103, 1, @on;

  EXEC sp_trace_setevent @traceid, 103, 10, @on;

  EXEC sp_trace_setevent @traceid, 103, 11, @on;

  EXEC sp_trace_setevent @traceid, 103, 14, @on;

  EXEC sp_trace_setevent @traceid, 103, 15, @on;

  EXEC sp_trace_setevent @traceid, 103, 34, @on;

  EXEC sp_trace_setevent @traceid, 103, 35, @on;

  EXEC sp_trace_setevent @traceid, 103, 37, @on;

  EXEC sp_trace_setevent @traceid, 103, 38, @on;

  EXEC sp_trace_setevent @traceid, 103, 39, @on;

  EXEC sp_trace_setevent @traceid, 103, 40, @on;

  EXEC sp_trace_setevent @traceid, 103, 42, @on;

  EXEC sp_trace_setstatus 1, 1;

   

  then I confirm trace has bem created:

  SELECT * FROM :: FN_TRACE_GETINFO(DEFAULT);

   

   

• Derrick Leggett

  SSCrazy

  Points: 2778

  More actions

  May 14, 2006 at 4:30 pm

  #637755

  and how are you deleting the trace?

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.

• Rachel Lee-244397

  SSC Veteran

  Points: 234

  More actions

  May 14, 2006 at 5:46 pm

  #637758

  EXEC sp_trace_setstatus 1, 2;

  but I don't want to have to delete the trace, before I can read the ouput file - is there not a way to do this?

   

  cheers

• Derrick Leggett

  SSCrazy

  Points: 2778

  More actions

  May 14, 2006 at 6:10 pm

  #637761

  Have you tried using status 0 instead?  Set the status to zero, move the file to a sub-directory, restart the trace.

  You can also always copy the file while it's still being written to.  That will give you a partial trace file.  I wouldn't recommend it though, unless you really feel like writing a fun parse routine.

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.

• Rachel Lee-244397

  SSC Veteran

  Points: 234

  More actions

  May 14, 2006 at 6:29 pm

  #637763

  thanks - it was supposed to be:

  EXEC sp_trace_setstatus 1, 0;

  but it looks like the only way for me to get what I want, would be to set the maxfilesize to be very small, so it would rollover more often.

   

  thanks for your help