WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, April 19, 2011

Getting SQL Server Startup Trace Flags with Powershell

Have you ever wanted to review what parameters you had set on your SQL Servers for startup?  Perhaps you wanted to standardize your trace flags?  The question was asked on twitter and a few folks responded.  Argenis Fernandez responded with a blog post http://www.sqlps.com/blog/?cat=5 on how to do it.  I have typically used the registry to get at this data but his method seemed cleaner.  In addition, if you are using newer equipment, you can query the WMI class "SqlServiceAdvancedProperty" but this doesn't seem to work in all cases.  The method I have worked up will inventory all the servers you choose and return a multi-dimensional array with the results.  It does not work with SQL 2000 Servers. 

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

# Does not work with SQL 2000 Instances( could use Get-Content or read from DB to get list)
$servers = @("Server1","Server2","Server3");

$serverStartupParms = @(); # 4D array (Sequence, Server, SQLInstance, TraceFlag)
$counter = 0;

Foreach($server in $servers) {

    # Get SQL Servers that are running on server
    try{
    $SQLServices = Get-WmiObject -ComputerName $server -Class win32_service -Filter "State='Running'" |
        where {$_.name -like "MSSQL$*" -or $_.name -eq "MSSQLSERVER"}       
    } catch{
        Write-Host "Error in Getting SQL Services:" $Error
    }
   
    $wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer" $server

    foreach($sqlServer in $SQLServices) {
        Write-Host "$server Getting Parms for "$sqlServer.Name
       
        $startupParameters = $wmiserver.Services[$sqlServer.Name].StartupParameters
       
        try{
            $traceFlags = $startupParameters.split(';') | Where { $_.StartsWith('-T') }
            foreach($flag in $traceFlags){
                $serverStartupParms += ,@($counter, $server,$sqlServer.Name,$flag) # 
                $counter++;
            }   
        }catch {Write-Host $server "-" $sqlServer.Name " is not returning StartupParameters"}   
    }
}

# Loop through each startup parm (EAV Style - 1 flag per "row") and push to screen
foreach($entry in $serverStartupParms){
    $serv = $entry.GetValue(1);
    $sqlServ = $entry.GetValue(2);
    $flag = $entry.GetValue(3);
   
    Write-Host $serv $sqlServ $flag
}

1 comment: