DBCC TRACEON (4199) affects all databases in a server

• onja

  Old Hand

  Points: 363

  More actions

  September 21, 2016 at 4:51 am

  #329898

  Hi,

  I'm about to execute DBCC TRACEON (4199) and sp_configure on a specific database but I'm not sure whether it will affect all other databases in a server even though I have already define a specific DB to where it should execute. What do you think?

  use [DB]

  DBCC TRACEON (4199)

  go

  use [DB]

  sp_configure 'max degree of parallelism', 6

  reconfigure

  go

  Thanks.

  Best regards,

  Paulo

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 21, 2016 at 4:55 am

  #1902113

  When the second parameter is not passed to DBCC TRACEON it only affects the current session. If you want to set it globally, you need to pass the value -1 as second parameter.

  That said, if you change it globally, it affects the whole instance.

  Same thing for setting MAXDOP to 6: it affects the whole instance.

  -- Gianluca Sartori

• onja

  Old Hand

  Points: 363

  More actions

  September 21, 2016 at 5:01 am

  #1902117

  So using 'USE [DB1]' does not guarantee that it will only affect DB1?

  Best regards,

  Paulo

• joeroshan

  SSChampion

  Points: 10377

  More actions

  September 21, 2016 at 5:05 am

  #1902118

  onja (9/21/2016)

  ---

  Hi,

  I'm about to execute DBCC TRACEON (4199) and sp_configure on a specific database but I'm not sure whether it will affect all other databases in a server even though I have already define a specific DB to where it should execute. What do you think?

  use [DB]

  DBCC TRACEON (4199)

  go

  use [DB]

  sp_configure 'max degree of parallelism', 6

  reconfigure

  go

  Thanks.

  Best regards,

  Paulo

  +1

  In both cases the database in context is ignored. The traceflag in your example enable at the session it is enabled. (eg the current SSMS query window) and the Maxdop setting affects instance level. You can use maxdop hint at query level though.

  ----------------------------------------------------------------------------------------------------------------------------------------------------
  Roshan Joe

  Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 21, 2016 at 7:39 am

  #1902160

  If you really want trace flag 4199 to apply to just one database you are limited to modifying every query in said database you want it applied to and apply the following:

  OPTION (QUERYTRACEON 4199)

  So if you want it on more than a few queries it isn't really a viable solution.

  I agree it would be useful to have 4199 (among other trace flags) scoped to an individual database!!

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• onja

  Old Hand

  Points: 363

  More actions

  September 21, 2016 at 10:18 pm

  #1902333

  Thank you guys