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

Friday, May 20, 2011

Cycling with Drugs, err Powershell

If you manage a large quantity of SQL Servers and want nothing to do with jobs on every one for things like log cycling, here is something you can run for each SQL Instance you manage.  It cycles the log based on the size.  Be sure to change the Invoke-SQLCMD3 to whatever "function" you use for running SQL commands via Powershell. 

function Invoke-SQLLogCycler2
param([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer, [int] $MBLimit = 30)

        $currentLog = $sqlServer.EnumErrorLogs() | Where-Object {$_.ArchiveNo -eq 0} |
            Select-Object -Property "Size"
        $currentLogSizeMB = [Math]::Truncate($currentLog.Size / 1MB);
        Write-Verbose "SQL Log for $SmoSqlServer is $currentLogSizeMB";
        if($currentLogSizeMB -gt $MBLimit){
            Invoke-Sqlcmd3 -Query "sp_cycle_errorlog" -ServerInstance $SmoSqlServer.Name
            return "SQL Log cycled for $SmoSqlServer with a size of $currentLogSizeMb MB (Threshold $MBLimit MB)";
        } else {
            return "SQL Log NOT cycled $SmoSqlServer with a size of $currentLogSizeMb MB (Threshold $MBLimit MB)";
        Write-Host " SQL Log Cycle Failed for " + $SmoSqlServer.Name + " " + $_.Error;

Invoke-SQLLogCycler2 -SmoSqlServer $sqlServer -MBLimit 25 | Out-GridView

1 comment:

  1. you have morels and smallmouth bass in your blood it seems too ... smallies are my favorite prey from my kayak when not in DBA land. I have a powershell to find the errorlog path and copy two log files to a central share, then cycle the errorlog. I have been looking for a good way to cycle as an SMO call, but it doesn't seem to be exposed. I'll have to call with some form of invoke-SQLcmd the way it looks.