Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by Anonymous on 31 March 2011

Pingback from  Dew Drop – March 31, 2011 | Alvin Ashcraft's Morning Dew

Posted by Steve Jones on 1 April 2011

That's a nice use of Powershell in a multi-user environment to add this quickly. Those backup messages really clutter up the log, and this is a great idea, IMHO, to limiting how much information you have to go through in a crisis.

Posted by Anonymous on 4 April 2011

Pingback from  @lotsahelp posts Change SQL Startup Parameters with PowerShell | sqlmashup

Posted by Mark D Powell on 5 April 2011

While the script is nice I do not think removing the backup messages from the error logs is a good idea.  I would rather have a script that read the log into a file filtering the backup messages out.

Posted by ALZDBA on 6 April 2011

This is a nice way to add our default startup trace flags to new instances at setup time.

Thanks.

Posted by Anonymous on 15 April 2011

Pingback from  Google Chrome 5 | Windows Errors Fix

Leave a Comment

Please register or log in to leave a comment.