Default trace in Production

• sizal0234

  SSCrazy

  Points: 2272

  More actions

  November 20, 2018 at 2:55 pm

  #416507

  EXEC sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  EXEC sp_configure 'default trace enabled';
  GO
  EXEC sp_configure 'show advanced options', 0;
  GO
  RECONFIGURE;
  GO

  I am running the above T-sql to check if default trace is enabled or disabled. 
  I want to enable it if it was disabled. How can I do that? Also, when I run the above code which column explains it is enabled. 
  Any immediate performance issue or downtime?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 20, 2018 at 3:02 pm

  #2014150

  sizal0234 - Tuesday, November 20, 2018 2:55 PM

  EXEC sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  EXEC sp_configure 'default trace enabled';
  GO
  EXEC sp_configure 'show advanced options', 0;
  GO
  RECONFIGURE;
  GO

  I am running the above T-sql to check if default trace is enabled or disabled. 
  I want to enable it if it was disabled. How can I do that? Also, when I run the above code which column explains it is enabled. 
  Any immediate performance issue or downtime?

  You can enable or disable with sp_configure like you just did with showing advanced options:
  --enable
sp_configure 'default trace enabled', 1
go
reconfigure
go

  --disable
sp_configure 'default trace enabled', 0
go
reconfigure
go
  No restarts or downtime required.

  Sue

• sizal0234

  SSCrazy

  Points: 2272

  More actions

  November 20, 2018 at 3:07 pm

  #2014151

  Thanks, and sorry If I am asking this again and just to confirm:
  How to check if it is already enabled?
  Thanks!

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 20, 2018 at 3:13 pm

  #2014153

  sizal0234 - Tuesday, November 20, 2018 3:07 PM

  Thanks, and sorry If I am asking this again and just to confirm:
  How to check if it is already enabled?
  Thanks!

  Just execute sp_configure 'default trace enabled' and it will come back with the config value and running value (0 false, 1 true).
  Usually they are the same but config value could be different if you haven't run the reconfigure after making the changes.

  Sue

• sizal0234

  SSCrazy

  Points: 2272

  More actions

  November 20, 2018 at 3:20 pm

  #2014154

  Here is the result. so it is enabled 🙂