none
Is a reboot / restart of an SQL sevrer or the windows server while enabling the TF 1118 and 1118

    Question

  • I have an asking from one of my end user. He want me to enable the TF 1117 and 1118. So I would like to know If there is a need of restarting SQL server for doing this.

    Wednesday, July 11, 2018 9:28 PM

All replies

  • restart not required

    try

    DBCC TRACESTATUS

    GO

    DBCC TRACEON (1117,-1); 

    DBCC TRACEON (1118,-1); 

    GO

    DBCC TRACESTATUS

    GO

    DBCC TRACEOFF (1117,-1); 
    DBCC TRACEOFF (1118,-1); 

    GO

    DBCC TRACESTATUS

    GO

    also see


    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017
    https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

    note, T1117 and 1118 are now the default behavior in 2016, 

    and are now controlled by ALTER DATABASE


    jchang

    Wednesday, July 11, 2018 10:38 PM
  • I have an asking from one of my end user. He want me to enable the TF 1117 and 1118. So I would like to know If there is a need of restarting SQL server for doing this.

    It depends.

    1) If you want to permanently enable them then add them as the new startup parameters in the SQL Server Configuration Manager and this does need a SQL reboot to take effect. The trace flags would then persist even after the server reboot unless explicitly removed from startup parameters.

    2) If you want to enable them only at the session/Global level then you can run DBCC TRACEON (with/without -1). These do not need a SQL reboot to take effect however, the trace flags do not persist through the server reboot.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, July 11, 2018 11:35 PM
  • I have an asking from one of my end user. He want me to enable the TF 1117 and 1118. So I would like to know If there is a need of restarting SQL server for doing this.

    What version of SQL Server we are talking ? For version SQL Server 2016 and above you can use alter database command to enable the trace flags database wise. So no need to restart at all

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, July 12, 2018 7:19 AM
    Moderator
  • Also, as of SQL Server 2016 both of these trace flags are no-ops - they do nothing nowadays.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Saturday, July 14, 2018 12:17 PM
    Moderator