SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Eliminating Successful Backup messages with Powershell and SMO

As a DBA, I enjoy knowing that the better job I do, the less likely it is that any of my customers know my name. In the same vein, I like my SQL Instances to behave in a similar fashion. I want the instance to remain silent unless I ask it a question or unless it has an issue – then I want it to scream loudly, but only so loud that the DBAs – not the customer – can hear it.

One setting of SQL Server I’ve grown to love is trace flag 3226. This trace flag has worked since SQL 2000, but was fully documented with SQL 2008R2 (http://msdn.microsoft.com/en-us/library/ms188396.aspx). With this flag enabled, gone are the countless messages that get in the way of seeing the real errors. While I understand logs can be filtered and software exists to parse logs, most of the time, these are unavailable. In environments with frequent log backups and numerous databases, this information can become overwhelming.

After enabling this flag globally, no longer is every successful backup recorded in the SQL Server error log nor is it placed in the Windows Application Event Log. That is all fine and dandy, however, how should I add this trace flag to 50+ instances? After weighing the options (clicking party, registry updates, WMI, SMO), I decided that SMO would be the most flexible given the situation.

Below is a Powershell script that will loop over all of the servers in a CMS group and add the 3226 trace flag to the StartupParameters property if it does not already exist.
$q = "`""

#timeout in seconds
If query does not return in this interval
timeout will fire and move to next object
prevents locks and blocking over this interval
set to zero to wait indefinitely
$QueryTimeout = 120


#Load SMO assemplies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null

$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]

foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;
Write-Host "Connected to $($srv.name)" -ForegroundColor Blue


#Get the managed computer object
$SQLServerWMI = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $srv.ComputerNamePhysicalNetBIOS

#$srv.ServiceName is not correct - derive service name here
Write-Host $srv.ServiceName
if($srv.InstanceName -eq "")
$ServiceName = "MSSQLSERVER"
$ServiceName = "MSSQL`$$($srv.InstanceName)"
#Get the info for this particular SQL Server Service
$SQLServerServiceWMI = $SQLServerWMI.Services[$ServiceName]
$StartupParameters = $SQLServerServiceWMI.StartupParameters
#Get the startupparameters into an array
$StartParamsArray = $StartupParameters.split(';')
if($StartParamsArray -notcontains "-T3226")
#Add T3226 to the StartupParameters collection
$SQLServerServiceWMI.StartupParameters += ';-T3226'
Write-Host "`tSuccessfully Added T3226 to $($srv.name)." -ForegroundColor Blue
Write-Host "`tT3226 already exists on $($srv.name)." -ForegroundColor Blue

Write-Host "Error Adding trace T3226 to $($srv.Name) on $($srv.ComputerNamePhysicalNetBios), "`
"service name $($ServiceName)." -BackgroundColor Red -ForegroundColor Black


Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.


Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...