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;

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;

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;

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';

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;

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

The post Working with the registry in SQL Server – adding trace flags to startup parameters appeared first on Wayne Sheffield.