WIDBA

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

Wednesday, March 2, 2011

Looking for trouble in all the right places with Powershell - Keeping a Clean Log

Part of monitoring sometimes is making sure the stuff you are checking is not so big it takes a long time to check it.  This function allows you to centrally manage your SQL Logs so they are cycled based on a number of days.  Many folks just implement this with a SQL job, but I am moving towards central management and less localized stuff.  The function below cycles based on the $DaysToCycle number.  An alternative implementation could be to read the current log and have it rollover on a specified number of entries.

Here is the function, taking an SMO SQL object which would already be created and the number of days.


Function Invoke-SQLLogCycler([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer, [int] $DaysToCycle)
{
    # Get the second newest log - the date stamped there is the last reboot date.
    $lastSQLLog = $SmoSqlServer.EnumErrorLogs() `
        | Where-Object {$_.ArchiveNo -eq 1} | Select-Object -Property "CreateDate"

    [DateTime] $lastLogRolloverDate = $lastSQLLog.CreateDate
   
    if($DaysToCycle -gt 0) {$DaysToCycle = $DaysToCycle * -1};


    # Check that last log cycle was more than specified number of days out (isPriorTo function below)
    if(isPriorTo -days $DaysToCycle -Date $lastLogRolloverDate)
    {
        try{
            $SmoSqlServer.JobServer.CycleErrorLog
            Write-Host "Error Cycled for " + $SmoSqlServer.Name;
        }
        catch{
            Write-Host " SQL Log Cycle Failed for " + $SmoSqlServer.Name;
        }
    }
    else
    {
        Write-Host "SQL Log was last cycled on " + $lastLogRolloverDate.ToLongDateString();
    }
}

# Helper function - makes code a bit more readable
Function isPriorTo([int]$days, [datetime]$Date)
{
    [DateTime]::Now.AddDays($days).Date -gt $Date.Date
}

Just a quick diversion from monitoring - but a nice one when you have several servers and no current management method.  

No comments:

Post a Comment