We need to use SQL Server Trace Flag 7646 to help mitigate some full text blocking, but I was dismayed to find that the trace flag "unset" itself, probably when the database server was rebooted.

I've used

DBCC TRACEON (7646, -1)

to set the trace flag and

DBCC TRACESTATUS

to show all trace flags, which told me that it wasn't set (after reboot, I guess). To set the trace flag permanently, I did this:

  • went to SQL Server Configuration Manager
  • viewed properties for SQL Server service
  • visited the Advanced tab
  • edited the Startup Parameters option

and added

;-T{tracenumber}

to the end, like so...

-dD:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T7646

Is that correct? I am finding conflicting results on the syntax for SQL Server Startup Parameters.

  • FYI: There's an article here on how to set the startup parameters through T-SQL: sqlservercentral.com/articles/Automation/105511 – JohnLBevan Nov 18 '16 at 11:55
  • ps. Per the above, the following SQL will give you the relevant registry paths & current values for your instance: select * from sys.dm_server_registry where value_name like 'SQLArg%' – JohnLBevan Nov 18 '16 at 12:02
up vote 14 down vote accepted

Yep, that'll about do it for you. Using the -T{traceflag} startup parameter, that is.

Something that I learned the hard way is that you have to have semicolons before each trace flag. For example, if you were enabling logging of deadlock info to file, your example would become...

-dD:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-
eD:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-
lD:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.l
df;-T7646;-T3605;-T1204;-T1222

Spaces between trace flags cause the later flags to be ignored.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.