Home > dba, powershell, SQLServerPedia Syndication > Change SQL Startup Parameters with PowerShell

Change SQL Startup Parameters with PowerShell

Backups Abound

The Annoyance

Sometimes when we’re trying to track down a problem and looking through SQL’s Logs we have to dig through hundreds of backup successful events just to find what we’re looking for. Unfortunately, SSMS doesn’t have an exclusion filter when viewing the logs, so I can’t filter them out that way. We also don’t care about having successful backups in these logs as the info is available elsewhere, including our management database.

The Solution

Trace flag 3226 will turn off logging of successful backups to the SQL Server error log, in addition to the Windows system event log. There are two ways to turn this on. Using the T-SQL command DBCC TRACEON(3226,-1), you immediately enable it, but only until SQL restarts as flags set this way do not survive a service restart. The other way to set it is by using startup parameters for the SQL service.

Don't Forget the Semicolons

In order to change SQL’s startup parameters, you need to:

  1. Load SQL Server Configuration Manager
  2. Right-click and bring up the Properties for the service you want to edit
  3. Click the Advanced tab
  4. Edit the Startup Parameters property by appending -T3226, remembering to separate with semicolons
  5. Click OK

The Better Solution

I wanted to do this quick and easy without all the clicks. I knew I wouldn’t just do this on one server, but maybe tens if not a hundred. So I opened up PowerShell ISE and started cranking out a script to this for me. My first hurdle was how are these startup parameters stored and how can I actually get to them to edit them. It turns out, they are just stored as registry keys. PowerShell works with the registry real well. After finding the key locations, doing some logic to make sure I change all instances on that server, I had my script. I made it generic enough so you could add any startup parameter to SQL, not just trace flags.

Calling the script to update all instances on the current computer is as easy as

PS> .\Add-SqlServerStartupParameter.ps1 '-T3226'

Caveats

The current version does not support SQL 2000, though I plan to release an update that will. I also plan to release a version that supports remote servers. If you are changing a clustered instance, make sure to change it on node the instance is running on as clustered instances overwrite registry settings with whatever their last values were when the instance was running. It’s best to change all nodes in a cluster at once, to be on the safe side.

Download the script: Add-SqlServerStartupParameter

  1. Adrienne
    March 2nd, 2012 at 14:56 | #1

    This was a godsend, thank you for posting! I am using it for post-unattended install configuration.

  2. John
    January 9th, 2013 at 13:59 | #2

    Does this also work for multiple trace like -T1117 and -T1118 at the same time or would i need to run it twice ?

    • January 9th, 2013 at 14:01 | #3

      It’s currently coded to do one at a time. I might update it to accept more when I have spare time.

  3. Derek
    February 8th, 2013 at 10:03 | #4

    What about adding it remotely? I would like to use this to add the flag to multiple servers.

    • February 8th, 2013 at 21:14 | #5

      It’s possible. This script just isn’t written for it properly.

  1. No trackbacks yet.