The following trace flags are essential for a variety of recovery scenarios. The use of trace flags allow the DBA to gain a finer granularity of control on the DBMS not normally given. Following are the list of important trace flags:
Remember that each flag has its own –T<trace#>. Do not try and use spaces or commas for multiple flags and a single –T command.
Always review trace output in the SQL Server error log to ensure start up flags has taken effect.
There are several ways to enable or disable a trace flag in SQL Server. Maybe the easiest way is to do it in the Sql Server Configuration Manager. In the Configuration Manager you then go to the properties of the SQL Server service (default is MSSQLSERVER). In the Advanced tab, select the StartUp parameters and add e.g ;-T4616 at the end and then click on OK button:
Now we have added the trace flag. It will be enabled as soon as we restart the SQL Server service.