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

$CMSInstance = "CMSINSTANCE"
   
#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
    
    try
    {
        
        #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"
        }
        else
        {
            $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'
            $SQLServerServiceWMI.Alter()
            Write-Host "`tSuccessfully Added T3226 to $($srv.name)." -ForegroundColor Blue
        }
        else
        {
            Write-Host "`tT3226 already exists on $($srv.name)." -ForegroundColor Blue
        }
    
    }
    catch
    {
        Write-Host "Error Adding trace T3226 to $($srv.Name) on $($srv.ComputerNamePhysicalNetBios), "`
        "service name $($ServiceName)." -BackgroundColor Red -ForegroundColor Black
    }
    
}