How to enable -T1118 trace flag in SQL Server 2012?

Question

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

Basin

Answer 1

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

Azral

could you please suggest where it get traced ? i mean any particular path ? – Basin

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 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. – Azral

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: sqluninterrupted.com/2012/02/23/… – Azral

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 ? – Basin

I don't see any mention of a .trc output file here, the MS documentation for this flag: support.microsoft.com/kb/328551 – Azral

so it means it internally get handle insted of using mixed extents it uses uniform extents. – Basin

Answer 2

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)

Odyssey

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 @Azral shows in his answer above. – Aside