8

I am using SQL Server 2012 I would like to enable -T1118 at server level can somebody please help me to enable it?

CJBS
  • 15,147
  • 6
  • 86
  • 135
Vikrant More
  • 5,182
  • 23
  • 58
  • 90

2 Answers2

18

SQL Server Configuration Manager can be used to set the SQL Server start-up parameters. You can specify the trace flag there:

  1. Start -> Run -> SQLServerManager11.msc (or just search for SQL Server Configuration Manager)

  2. In SQL Server Configuration Manager, click SQL Server Services.

  3. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

  4. On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T1118), and then click Add.

  5. Click OK.

  6. Restart the Database Engine.

Have a look at this link for details: http://technet.microsoft.com/en-us/library/ms345416.aspx

CJBS
  • 15,147
  • 6
  • 86
  • 135
  • could you please suggest where it get traced ? i mean any particular path ? – Vikrant More Mar 03 '14 at 05:45
  • 2
    I'm not sure what you mean by 'where it gets traced'. SQL Server trace flags are usually used for testing/debugging or investigating performance problems. See http://technet.microsoft.com/en-us/library/ms188396.aspx for details. They're used to alter the default behaviour of SQL Server. They don't always produce some sort of output, such as to a file. I haven't used this particular trace flag -- the instructions that I provided to your question are generic - i.e. they'd work with any trace flag number as part of SQL Server's start-up command-line options. – CJBS Mar 03 '14 at 06:43
  • 1
    I'd suggest doing thorough research before using a trace flag, ESPECIALLY in production. Here are some details that I found about trace flag 1118: http://sqluninterrupted.com/2012/02/23/did-you-know-trace-flag-1118-does-not-just-affect-your-tempdb/ – CJBS Mar 03 '14 at 06:44
  • i mean -T1118 trace flag should keep tracking these data in .trc file i would like to know at what location it will get created ? – Vikrant More Mar 03 '14 at 10:54
  • 1
    I don't see any mention of a .trc output file here, the MS documentation for this flag: http://support.microsoft.com/kb/328551 – CJBS Mar 03 '14 at 17:53
  • so it means it internally get handle insted of using mixed extents it uses uniform extents. – Vikrant More Mar 04 '14 at 05:37
0

Enable for current session and only for me:

    DBCC TRACEON(1118)

Enable for current session and everyone who is accessing server(global level settings)

    DBCC TRACEON(1118,-1)
Channa
  • 742
  • 17
  • 28
  • 2
    This will turn it on _Temporarily_, but as soon as the Server Reboots or Restarts, the TraceFlags will all be Reset (Turned Off). To persist the Flag Settings between cycling, you will need to enable it as a Start-Up Parameter as @CJBS shows in his answer above. – MikeTeeVee Jun 07 '19 at 23:23