Working with the registry in SQL Server – adding trace flags to startup parameters

2017-09-04 Wayne Sheffield 5 comments

In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.

In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.

Let’s start off with how the environment is configured. The below screen shot (from SQL Server Configuration Manager) shows the startup parameters now in use:

Notice that this instance has only TF 1204 enabled.

Retrieving the Startup Parameters

The registry key “HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters” stores the startup parameters. Especially relevant is the “MSSQL12.SQL2014” portion, which will vary depending on the SQL Server version and your instance name. However, if you remember from the earlier post, there are instance-aware versions of the registry extended stored procedures that automatically handle this. For the instance-aware versions, use this registry key instead: “HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters”.

To get all the startup parameters, we would use the xp_instance_regenumvalues extended stored procedure. As noted in the earlier post, this produces a result set for each value. Therefore, this example loads them into a table variable and then displays this table:

DECLARE @RegHive    VARCHAR(50),
        @RegKey     VARCHAR(100);

SET @RegHive = 'HKEY_LOCAL_MACHINE';
SET @RegKey  = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';

-- Get all of the arguments / parameters when starting up the service.
DECLARE @SQLArgs TABLE (
    Value   VARCHAR(50),
    Data    VARCHAR(500),
    ArgNum  AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', '')));

INSERT INTO @SQLArgs
    EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey;

SELECT * FROM @SQLArgs;

DECLARE @RegHive VARCHAR(50),

@RegKey VARCHAR(100);

SET @RegHive = 'HKEY_LOCAL_MACHINE';

SET @RegKey = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';

-- Get all of the arguments / parameters when starting up the service.

DECLARE @SQLArgs TABLE (

Value VARCHAR(50),

Data VARCHAR(500),

ArgNum AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', '')));

INSERT INTO @SQLArgs

EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey;

SELECT * FROM @SQLArgs;

This script could have omitted the two variables, but since I’m planning on using them for updating the registry, I decided to store their locations in variables and just use those instead. Additionally, since I’ll be adding to and removing from this list and I want to keep the parameters in the same order, I have added a computed column to calculate the argument number. Running this script shows all the startup parameters:

This matches the earlier screen shot. Running the following will show the running trace flags:

DBCC TRACESTATUS;

1	DBCC TRACESTATUS;

Enabling / disabling trace flags

The next step that needs performed is to enable trace flags 1117 and 1118, and to disable trace flags 1204 and 1222. The commands DBCC TRACEON and DBCC TRACEOFF are used to accomplish this. These commands take a comma-delimited list of trace flags. To perform their action at a global level, use the optional flag of “-1”. These commands set and verify the trace flags:

DBCC TRACEOFF (1204, 1222, -1);
DBCC TRACEON (1117, 1118, -1); 
DBCC TRACESTATUS;

DBCC TRACEOFF (1204, 1222, -1);

DBCC TRACEON (1117, 1118, -1);

DBCC TRACESTATUS;

Setting trace flags to handle the instance restarting

This will start and stop the trace flags immediately; however this has not changed their startup state when the instance next restarts. Consequently, we need to modify the registry to remove TF 1204 and to add TFs 1117 and 1118. The following statements will override SQLArg3 with the TF 1117, and add TF 1118 to SQLArg4. Remember that writing to the registry requires access for the service account to the specified keys. Optionally, the service account can be a member of the Local Administrators group. In order for SQL Server to pick up this change, the service must be restarted after these changes have been made.

EXECUTE master.sys.xp_instance_regwrite
    @RegHive, @RegKey, 'SQLArg3', 'REG_SZ', '-T1117';
EXECUTE master.sys.xp_instance_regwrite
    @RegHive, @RegKey, 'SQLArg4', 'REG_SZ', '-T1118';

EXECUTE master.sys.xp_instance_regwrite

@RegHive, @RegKey, 'SQLArg3', 'REG_SZ', '-T1117';

EXECUTE master.sys.xp_instance_regwrite

@RegHive, @RegKey, 'SQLArg4', 'REG_SZ', '-T1118';

Cleaning up the registry

If we remove more values than what were added, we might need to call master.sys.xp_instance_regdeletevalue. Additionally, if we are only deleting some values, there might be gaps left in the argument number which need eliminated. The following script enables and/or disables current trace flags, modifies the registry for the startup parameters, and handles the registry consolidation / cleanup of this key.

/******************************************************************************
Globally enable / disable the specified trace flags.
Use DBCC TRACEON/TRACEOFF to enable disable globally trace flags, then adjust
the SQL Server instance startup parameters for these trace flags.

SQL Server startup parameters are stored in the registry at:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters

To use the xp_instance_reg... XPs, use:
HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters.

To use:
1. Add the Trace Flags that you want modified to the @TraceFlags table variable.
2. Set the @DebugLevel variable to 1 to see what will happen on your system first.
3. When satisified what will happen, set @DebugLevel to 0 to actually execute the statements.
********************************************************************************
                               MODIFICATION LOG
********************************************************************************
2016-08-03 WGS Initial Creation.
*******************************************************************************/
SET NOCOUNT ON;
-- Declare and initialize variables.
-- To use with SQL 2005, cannot set the variables in the declare statement.
DECLARE @MaxValue   INTEGER,
        @SQLCMD     VARCHAR(MAX),
        @RegHive    VARCHAR(50),
        @RegKey     VARCHAR(100),
        @DebugLevel TINYINT;

SET @RegHive = 'HKEY_LOCAL_MACHINE';
SET @RegKey  = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @DebugLevel = 0;  -- only makes changes if set to zero!

-- Add the trace flags that you want changed here.
-- If enable = 1, DBCC TRACEON will be run; if enable = 0 then DBCC TRACEOFF will be run.
-- If enable_on_startup = 1, then this TF will be added to start up on service restart; 
-- If enable_on_startup - 0, then this TF will be removed from starting up service restart
DECLARE @TraceFlags TABLE (
    TF                  INTEGER, 
    enable              BIT, 
    enable_on_startup   BIT,
    TF2                 AS '-T' + CONVERT(VARCHAR(15), TF)
);
INSERT INTO @TraceFlags (TF, enable, enable_on_startup)
-- To work with SQL 2005, cannot use a table value constructor.
-- So, use SELECT statements with UNION ALL for each TF to modify.
SELECT 1117, 1, 1 UNION ALL
SELECT 1118, 1, 1 UNION ALL
SELECT 1204, 0, 0 UNION ALL
SELECT 1222, 0, 0;


-- Get all of the arguments / parameters when starting up the service.
DECLARE @SQLArgs TABLE (
    Value   VARCHAR(50), 
    Data    VARCHAR(500),
    ArgNum  AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', '')));
INSERT INTO @SQLArgs
    EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey;


-- Get the highest argument number that is currently set
SELECT  @MaxValue = MAX(ArgNum) 
FROM    @SQLArgs;
RAISERROR('MaxValue: %i', 10, 1, @MaxValue) WITH NOWAIT;

-- Disable specified trace flags
SELECT  @SQLCMD = 'DBCC TRACEOFF(' + 
        STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF)
               FROM   @TraceFlags
               WHERE  enable = 0
               ORDER BY TF
               FOR XML PATH(''), TYPE).value('.','varchar(max)')
              ,1,1,'') + ', -1);'

IF @DebugLevel = 0 EXECUTE (@SQLCMD);
RAISERROR('Disable TFs Command: "%s"', 10, 1, @SQLCMD) WITH NOWAIT;

-- Enable specified trace flags
SELECT  @SQLCMD = 'DBCC TRACEON(' + 
        STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF)
               FROM   @TraceFlags
               WHERE  enable = 1
               ORDER BY TF
               FOR XML PATH(''), TYPE).value('.','varchar(max)')
              ,1,1,'') + ', -1);'

IF @DebugLevel = 0 EXECUTE (@SQLCMD);
RAISERROR('Enable TFs Command:  "%s"', 10, 1, @SQLCMD) WITH NOWAIT;

DECLARE cSQLParams CURSOR LOCAL FAST_FORWARD FOR
WITH cte AS
(
    -- Current arguments, with new TFs added at the end. Get a row number to sort by.
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY ISNULL(ArgNum, 999999999), TF) - 1 AS RN
    FROM    @SQLArgs arg
    FULL OUTER JOIN @TraceFlags tf ON arg.Data = tf.TF2
), cte2 AS
(
    -- Use the row number to calc the SQLArg# for new TFs. 
    -- Use the original Value (SQLArg#) and Data for all rows if possible, 
    -- Otherwise use the calculated SQLArg# and the calculated TF2 column.
    -- Only get the original non-TF-matched parameters, and the TFs set to be enabled
    -- (existing startup TFs not in @TraceFlags are left alone).
    SELECT  ca.Value,
            ca.Data
            -- in case any TFs are removed, calculate new row numbers in order 
            -- to renumber the SQLArg values
            , ROW_NUMBER() OVER (ORDER BY RN) - 1 AS RN2
    FROM    cte
            -- Again, for SQL 2005, use SELECT statement instead of VALUES.
            CROSS APPLY (SELECT ISNULL(Value, 'SQLArg' + CONVERT(VARCHAR(15), RN)), 
                                ISNULL(Data, TF2) ) ca(Value, Data)
    WHERE   ISNULL(enable_on_startup, 1) = 1  -- ISNULL handles non-TF parameters
)
-- The first three parameters are the location of the errorlog directory,
-- and the master database file locations. Ignore these.
-- This returns the remaining parameters that should be set.
-- Also return the highest number of parameters, so can determine if any need to be deleted.
SELECT  'SQLArg' + CONVERT(VARCHAR(15), RN2) AS Value,
        Data,
        MAX(RN2) OVER () AS MaxRN2
FROM    cte2
WHERE   RN2 > 2
ORDER BY RN2;

DECLARE @Value VARCHAR(50),
        @Data  VARCHAR(500),
        @MaxRN2 INTEGER;
OPEN cSQLParams;
FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2;
WHILE @@FETCH_STATUS = 0 
BEGIN
    IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regwrite @RegHive, @RegKey, @Value, 'REG_SZ', @Data;
    RAISERROR('EXECUTE master.sys.xp_instance_regwrite ''%s'', ''%s'', ''%s'', ''REG_SZ'', ''%s''', 10, 1, @RegHive, @RegKey, @Value, @Data) WITH NOWAIT;
    FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2;
END;
CLOSE cSQLParams;
DEALLOCATE cSQLParams;

-- In case deleting more TFs than added, there may be extra SQLArg values left behind. 
-- Need to delete the extras now.
WHILE @MaxValue > @MaxRN2
BEGIN
  SET @Value = 'SQLArg' + CONVERT(VARCHAR(15), @MaxValue);
  IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regdeletevalue @RegHive, @RegKey, @Value;
    RAISERROR('EXECUTE master.sys.xp_instance_regdeletevalue ''%s'', ''%s'', ''%s''', 10, 1, @RegHive, @RegKey, @Value) WITH NOWAIT;
  SET @MaxValue = @MaxValue - 1;
END;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

/******************************************************************************

Globally enable / disable the specified trace flags.

Use DBCC TRACEON/TRACEOFF to enable disable globally trace flags, then adjust

the SQL Server instance startup parameters for these trace flags.

SQL Server startup parameters are stored in the registry at:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters

To use the xp_instance_reg... XPs, use:

HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters.

To use:

1. Add the Trace Flags that you want modified to the @TraceFlags table variable.

2. Set the @DebugLevel variable to 1 to see what will happen on your system first.

3. When satisified what will happen, set @DebugLevel to 0 to actually execute the statements.

********************************************************************************

MODIFICATION LOG

********************************************************************************

2016-08-03 WGS Initial Creation.

*******************************************************************************/

SET NOCOUNT ON;

-- Declare and initialize variables.

-- To use with SQL 2005, cannot set the variables in the declare statement.

DECLARE @MaxValue INTEGER,

@SQLCMD VARCHAR(MAX),

@RegHive VARCHAR(50),

@RegKey VARCHAR(100),

@DebugLevel TINYINT;

SET @RegHive = 'HKEY_LOCAL_MACHINE';

SET @RegKey = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';

SET @DebugLevel = 0; -- only makes changes if set to zero!

-- Add the trace flags that you want changed here.

-- If enable = 1, DBCC TRACEON will be run; if enable = 0 then DBCC TRACEOFF will be run.

-- If enable_on_startup = 1, then this TF will be added to start up on service restart;

-- If enable_on_startup - 0, then this TF will be removed from starting up service restart

DECLARE @TraceFlags TABLE (

TF INTEGER,

enable BIT,

enable_on_startup BIT,

TF2 AS '-T' + CONVERT(VARCHAR(15), TF)

);

INSERT INTO @TraceFlags (TF, enable, enable_on_startup)

-- To work with SQL 2005, cannot use a table value constructor.

-- So, use SELECT statements with UNION ALL for each TF to modify.

SELECT 1117, 1, 1 UNION ALL

SELECT 1118, 1, 1 UNION ALL

SELECT 1204, 0, 0 UNION ALL

SELECT 1222, 0, 0;

-- Get all of the arguments / parameters when starting up the service.

DECLARE @SQLArgs TABLE (

Value VARCHAR(50),

Data VARCHAR(500),

ArgNum AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', '')));

INSERT INTO @SQLArgs

EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey;

-- Get the highest argument number that is currently set

SELECT @MaxValue = MAX(ArgNum)

FROM @SQLArgs;

RAISERROR('MaxValue: %i', 10, 1, @MaxValue) WITH NOWAIT;

-- Disable specified trace flags

SELECT @SQLCMD = 'DBCC TRACEOFF(' +

STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF)

FROM @TraceFlags

WHERE enable = 0

ORDER BY TF

FOR XML PATH(''), TYPE).value('.','varchar(max)')

,1,1,'') + ', -1);'

IF @DebugLevel = 0 EXECUTE (@SQLCMD);

RAISERROR('Disable TFs Command: "%s"', 10, 1, @SQLCMD) WITH NOWAIT;

-- Enable specified trace flags

SELECT @SQLCMD = 'DBCC TRACEON(' +

STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF)

FROM @TraceFlags

WHERE enable = 1

ORDER BY TF

FOR XML PATH(''), TYPE).value('.','varchar(max)')

,1,1,'') + ', -1);'

IF @DebugLevel = 0 EXECUTE (@SQLCMD);

RAISERROR('Enable TFs Command: "%s"', 10, 1, @SQLCMD) WITH NOWAIT;

DECLARE cSQLParams CURSOR LOCAL FAST_FORWARD FOR

WITH cte AS

(

-- Current arguments, with new TFs added at the end. Get a row number to sort by.

SELECT *,

ROW_NUMBER() OVER (ORDER BY ISNULL(ArgNum, 999999999), TF) - 1 AS RN

FROM @SQLArgs arg

FULL OUTER JOIN @TraceFlags tf ON arg.Data = tf.TF2

), cte2 AS

(

-- Use the row number to calc the SQLArg# for new TFs.

-- Use the original Value (SQLArg#) and Data for all rows if possible,

-- Otherwise use the calculated SQLArg# and the calculated TF2 column.

-- Only get the original non-TF-matched parameters, and the TFs set to be enabled

-- (existing startup TFs not in @TraceFlags are left alone).

SELECT ca.Value,

ca.Data

-- in case any TFs are removed, calculate new row numbers in order

-- to renumber the SQLArg values

, ROW_NUMBER() OVER (ORDER BY RN) - 1 AS RN2

FROM cte

-- Again, for SQL 2005, use SELECT statement instead of VALUES.

CROSS APPLY (SELECT ISNULL(Value, 'SQLArg' + CONVERT(VARCHAR(15), RN)),

ISNULL(Data, TF2) ) ca(Value, Data)

WHERE ISNULL(enable_on_startup, 1) = 1 -- ISNULL handles non-TF parameters

)

-- The first three parameters are the location of the errorlog directory,

-- and the master database file locations. Ignore these.

-- This returns the remaining parameters that should be set.

-- Also return the highest number of parameters, so can determine if any need to be deleted.

SELECT 'SQLArg' + CONVERT(VARCHAR(15), RN2) AS Value,

Data,

MAX(RN2) OVER () AS MaxRN2

FROM cte2

WHERE RN2 > 2

ORDER BY RN2;

DECLARE @Value VARCHAR(50),

@Data VARCHAR(500),

@MaxRN2 INTEGER;

OPEN cSQLParams;

FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2;

WHILE @@FETCH_STATUS = 0

BEGIN

IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regwrite @RegHive, @RegKey, @Value, 'REG_SZ', @Data;

RAISERROR('EXECUTE master.sys.xp_instance_regwrite ''%s'', ''%s'', ''%s'', ''REG_SZ'', ''%s''', 10, 1, @RegHive, @RegKey, @Value, @Data) WITH NOWAIT;

FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2;

END;

CLOSE cSQLParams;

DEALLOCATE cSQLParams;

-- In case deleting more TFs than added, there may be extra SQLArg values left behind.

-- Need to delete the extras now.

WHILE @MaxValue > @MaxRN2

BEGIN

SET @Value = 'SQLArg' + CONVERT(VARCHAR(15), @MaxValue);

IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regdeletevalue @RegHive, @RegKey, @Value;

RAISERROR('EXECUTE master.sys.xp_instance_regdeletevalue ''%s'', ''%s'', ''%s''', 10, 1, @RegHive, @RegKey, @Value) WITH NOWAIT;

SET @MaxValue = @MaxValue - 1;

END;

After running, you can see that the changes have in fact been changed:

Repost, SQL none

#1 | Written by Richard L. Dawson about 7 years ago. Reply

Nicely Done!!
#2 | Written by Shah about 7 years ago. Reply

In SQL Server 2016, the trace flag 1117 which controls simultaneous growth of the TempDB files, and trace flag 1118 which reduces page latch contention, are now enabled by default.
- #3 | Written by Wayne Sheffield about 7 years ago. Reply
  
  Hi Shah,
  Thanks for your comment. Actually, the functionality of these is enabled by default in the tempdb database. Those trace flags are now obsolete and no longer do anything starting with SQL Server 2016. Both of these are now database level settings (whereas the trace flags were server level and applied to all databases). Mixed page allocations (TF 1118) are set with the ALTER DATABASE SET MIXED_PAGE_ALLOCATIONS ON/OFF statement, and simultaneous growth of all files in the same filegroup (TF 1117) are set with the ALTER DATABASE MODIFY FILEGROUP AUTOGROW_ALL_FILES statement. See //docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options and //docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options for details on these statements.
#4 | Written by Jeff Moden about 4 years ago. Reply

Every once in a while, I have to do startup checks and sets, especially when setting up new servers and automation makes for “Martha Stuart Moments”. I never did thank you for this post and, although years late, better late than never.

So, with that being said, THANK YOU! Keep hitting them hard and far, Wayne!
#5 | Written by Jeff Moden about 4 years ago. Reply

As a passing note, MS making it so you CANNOT override the settings on TempDB for the equivalent of TF 1117 really put the screws to me in the area of temporary work especially since they still haven’t fixed the bug with SET IDENTITY_INSERT ON and how it beats the hell out of TempDB even when a sort isn’t required.

This is yet another example of MS trying to make things “idiot proof”. They’ve forgotten that if you make something “idiot proof”, only idiots will use it. 😀

#1 | Pinged by Dew Drop – September 5, 2017 (#2554) – Morning Dew about 7 years ago.

[…] Working with the registry in SQL Server – adding trace flags to startup parameters (Wayne Sheffield) […]
#2 | Pinged by Dealing With Trace Flags In The Registry – Curated SQL about 7 years ago.

[…] Wayne Sheffield shows us how to configure SQL Server trace flags within T-SQL using registry access …: […]
#3 | Pinged by Common Tempdb Trace Flags – Back to Basics | SQL RNNR about 7 years ago.

[…] do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was […]
#4 | Pinged by File Growth Trace Flags – Curated SQL about 7 years ago.

[…] do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was […]

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

Working with the registry in SQL Server – adding trace flags to startup parameters

Retrieving the Startup Parameters

Enabling / disabling trace flags

Setting trace flags to handle the instance restarting

Cleaning up the registry

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

Working with the registry in SQL Server – adding trace flags to startup parameters

Retrieving the Startup Parameters

Enabling / disabling trace flags

Setting trace flags to handle the instance restarting

Cleaning up the registry

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply