Unable to start the trace

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  October 16, 2018 at 8:57 am

  #365785

  I have this piece of code which I executed but the trace isn't starting. Any idea?
  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 1024

  exec @rc = sp_trace_create @TraceID output, 0, N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.FIRSTTRY\MSSQL\Log\FirstTryTrace.trc', @maxfilesize, NULL

  if (@rc != 0) goto error

  -- Set the events

  declare @on bit

  set @on = 1

  exec sp_trace_setevent @TraceID, 14, 10, @on

  exec sp_trace_setevent @TraceID, 14, 11, @on

  exec sp_trace_setevent @TraceID, 14, 6, @on

  exec sp_trace_setevent @TraceID, 14, 12, @on

  exec sp_trace_setevent @TraceID, 15, 11, @on

  exec sp_trace_setevent @TraceID, 15, 6, @on

  exec sp_trace_setevent @TraceID, 15, 10, @on

  exec sp_trace_setevent @TraceID, 15, 12, @on

  exec sp_trace_setevent @TraceID, 17, 10, @on

  exec sp_trace_setevent @TraceID, 17, 11, @on

  exec sp_trace_setevent @TraceID, 17, 6, @on

  exec sp_trace_setevent @TraceID, 17, 12, @on

  exec sp_trace_setevent @TraceID, 10, 10, @on

  exec sp_trace_setevent @TraceID, 10, 6, @on

  exec sp_trace_setevent @TraceID, 10, 11, @on

  exec sp_trace_setevent @TraceID, 10, 12, @on

  exec sp_trace_setevent @TraceID, 12, 11, @on

  exec sp_trace_setevent @TraceID, 12, 6, @on

  exec sp_trace_setevent @TraceID, 12, 10, @on

  exec sp_trace_setevent @TraceID, 12, 12, @on

  exec sp_trace_setevent @TraceID, 13, 11, @on

  exec sp_trace_setevent @TraceID, 13, 6, @on

  exec sp_trace_setevent @TraceID, 13, 10, @on

  exec sp_trace_setevent @TraceID, 13, 12, @on

  -- Set the Filters

  declare @intfilter int

  declare @bigintfilter bigint

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8ff968f5-5c01-42ac-b5ec-a17fe5f07a15'

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

  --must stop a trace before it can be closed. Must be closed before you can access the file

  exec sp_trace_setstatus TRACEID, 1 --start trace

  --exec sp_trace_setstatus TRACEID, 0 --stop trace

  --exec sp_trace_setstatus TRACEID, 2 --close trace

  select * from ::fn_trace_getinfo(default)

  "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

• jasona.work

  SSC Guru

  Points: 50117

  More actions

  October 16, 2018 at 9:05 am

  #2009889

  And what is the error you're getting, either when you run the code or in the error log?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 16, 2018 at 9:08 am

  #2009892

  What error are you getting?

  Note that you don't have to run sp_trace_setstatus separately. It's part of the script already,

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  October 16, 2018 at 9:09 am

  #2009893

  jasona.work - Tuesday, October 16, 2018 9:05 AM

  And what is the error you're getting, either when you run the code or in the error log?

  When I run this command exec sp_trace_setstatus TRACEID, 1 --start trace

  I get this message

  Command run successfully
  But when I run this select * from ::fn_trace_getinfo(default)
  I see the value of property 1 is set to 0 which means trace isn't running.  

  "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  October 16, 2018 at 9:14 am

  #2009897

  GilaMonster - Tuesday, October 16, 2018 9:08 AM

  What error are you getting?

  Note that you don't have to run sp_trace_setstatus separately. It's part of the script already,

  I deleted the trace, ran it again like this but still no luck.
  

  /****************************************************/

  /* Created by: SQL Server vNext CTP2.0 Profiler */

  /* Date: 10/16/2018 08:17:37 AM */

  /****************************************************/

  -- Create a Queue

  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 1024

  exec @rc = sp_trace_create @TraceID output, 0, N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.FIRSTTRY\MSSQL\Log\FirstTryTrace.trc', @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, 14, 10, @on

  exec sp_trace_setevent @TraceID, 14, 11, @on

  exec sp_trace_setevent @TraceID, 14, 6, @on

  exec sp_trace_setevent @TraceID, 14, 12, @on

  exec sp_trace_setevent @TraceID, 15, 11, @on

  exec sp_trace_setevent @TraceID, 15, 6, @on

  exec sp_trace_setevent @TraceID, 15, 10, @on

  exec sp_trace_setevent @TraceID, 15, 12, @on

  exec sp_trace_setevent @TraceID, 17, 10, @on

  exec sp_trace_setevent @TraceID, 17, 11, @on

  exec sp_trace_setevent @TraceID, 17, 6, @on

  exec sp_trace_setevent @TraceID, 17, 12, @on

  exec sp_trace_setevent @TraceID, 10, 10, @on

  exec sp_trace_setevent @TraceID, 10, 6, @on

  exec sp_trace_setevent @TraceID, 10, 11, @on

  exec sp_trace_setevent @TraceID, 10, 12, @on

  exec sp_trace_setevent @TraceID, 12, 11, @on

  exec sp_trace_setevent @TraceID, 12, 6, @on

  exec sp_trace_setevent @TraceID, 12, 10, @on

  exec sp_trace_setevent @TraceID, 12, 12, @on

  exec sp_trace_setevent @TraceID, 13, 11, @on

  exec sp_trace_setevent @TraceID, 13, 6, @on

  exec sp_trace_setevent @TraceID, 13, 10, @on

  exec sp_trace_setevent @TraceID, 13, 12, @on

  -- Set the Filters

  declare @intfilter int

  declare @bigintfilter bigint

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8ff968f5-5c01-42ac-b5ec-a17fe5f07a15'

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

  --must stop a trace before it can be closed. Must be closed before you can access the file

  exec sp_trace_setstatus 2, 1 --start trace

  --exec sp_trace_setstatus 2, 0 --stop trace

  --exec sp_trace_setstatus 2, 2 --close trace

  "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 16, 2018 at 9:15 am

  #2009898

  Again, you don't have to run the setstatus separately (and you probably shouldn't), the script already contains the line to start the trace.

  

  So leave off your additions, and just run the  code up to the GO. What errors do you get?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  October 16, 2018 at 11:28 am

  #2009923

  GilaMonster - Tuesday, October 16, 2018 9:15 AM

  Again, you don't have to run the setstatus separately (and you probably shouldn't), the script already contains the line to start the trace.

  

  So leave off your additions, and just run the  code up to the GO. What errors do you get?

  I ran the command, removed extra line, I got this in a message box
  

  TraceID
  2
  But when I ran this
  select * from ::fn_trace_getinfo(default)
  I get 0 for traceID 2 which means it isn't running right?

  "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 16, 2018 at 11:31 am

  #2009924

  SELECT * FROM sys.traces (fn_trace_getinfo has been deprecated for years)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  October 16, 2018 at 11:40 am

  #2009931

  GilaMonster - Tuesday, October 16, 2018 11:31 AM

  SELECT * FROM sys.traces (fn_trace_getinfo has been deprecated for years)

  Thanks!

  "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]