trace not working

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  June 4, 2002 at 1:14 am

  #157912

  Hi all,

  I used SQL 2000 Profiler to trace certain table read access. I have set up event and filter, and it works well. In order to run the trace automatically, I convert the set up to script code using SQL 2000 Profiler->File->Script Trace->For SQL 2000 and create a stored procedure to run this script code. Unfortunatelly, the drive could not be filled up. I am wondering why? The following is my code:

  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 5

  -- Please replace the text InsertFileNameHere, with an appropriate

  -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

  -- will be appended to the filename automatically. If you are writing from

  -- remote server to local drive, please use UNC path and make sure server has

  -- write access to your network share

  exec @rc = sp_trace_create @TraceID output, 2, N'\\PC723\C$\TbmasterTrace\TbmasterTry3', @maxfilesize, NULL

  if (@rc != 0) goto error

  -- Client side File and Table cannot be scripted

  -- Set the events

  declare @on bit

  set @on = 1

  exec sp_trace_setevent @TraceID, 13, 1, @on

  exec sp_trace_setevent @TraceID, 13, 10, @on

  exec sp_trace_setevent @TraceID, 13, 11, @on

  exec sp_trace_setevent @TraceID, 13, 12, @on

  exec sp_trace_setevent @TraceID, 13, 13, @on

  exec sp_trace_setevent @TraceID, 13, 14, @on

  exec sp_trace_setevent @TraceID, 13, 16, @on

  exec sp_trace_setevent @TraceID, 41, 1, @on

  exec sp_trace_setevent @TraceID, 41, 10, @on

  exec sp_trace_setevent @TraceID, 41, 11, @on

  exec sp_trace_setevent @TraceID, 41, 12, @on

  exec sp_trace_setevent @TraceID, 41, 13, @on

  exec sp_trace_setevent @TraceID, 41, 14, @on

  exec sp_trace_setevent @TraceID, 41, 16, @on

  -- Set the Filters

  declare @intfilter int

  declare @bigintfilter bigint

  exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%select%tbmaster%'

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'

  set @intfilter = 100

  exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

  -- Set the trace status to start

  exec sp_trace_setstatus @TraceID, 1

  -- display trace id for future references

  select TraceID=@TraceID

  goto finish

  error:

  select ErrorCode=@rc

  finish:

  GO

  The trace file can be created, but it is empty. Is there any one can help?

  Many thanks

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA

• Antares686

  SSC Guru

  Points: 125444

  More actions

  June 4, 2002 at 5:27 am

  #430296

  Try stoping and closing the trace

  exec sp_trace_setstatus @TraceID, 0

  exec sp_trace_setstatus @TraceID, 2

  and see if the output doesn't dump then. I tested and code works fine for me. But I believe the dumps only occurr when so much data is in the buffer or when a stop is called. Also you stated you want to capture a specific table access but here

  exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%select%tbmaster%'

  you are using OR logic which means it will track everything ((app not like SQLAgent% AND app not like SQLProfiler AND ObjectID >= 100) OR (text is like %select%tbmaster%)). SO you catch everything for the one table and anything else where not the other parameters. Just wasn't sure if your logic was right.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  June 9, 2002 at 10:33 pm

  #430561

  Thanks Antares,

  It works.....

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA