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

Powershell Monitoring drive space - improvements

 Here is an improvement on finding drives that are running low on space.  It returns the drive, size, free space and the amount of MB of space you want minimally (based on the $thresholdMultiplier).

## Return any physical drive on server(note $filter) that has less than 20% free space ($thresholdMultiplier)
function Get-DriveSpace
    param( [Parameter(Mandatory=$true)]$computer , [int]$thresholdMultiplier=.20 )
    [string]$filter = "DriveType = '3'"

        Get-WmiObject -class win32_logicaldisk -filter $filter -comp $computer |
            Select-Object Name,@{label="SizeMB";Expression={[Math]::Truncate($_.Size / 1MB)}},
            @{label="FreeMB";Expression={[Math]::Truncate($_.FreeSpace / 1MB)}},
            @{label="WarningMB";Expression={[Math]::Truncate(($_.Size * $thresholdMultiplier) /1MB)}}|
            Where-Object{$_.FreeMB -le $_.WarningMB}
    } catch {
        Write-Verbose "Error $Error in getting disk space for $computer"

Wednesday, May 18, 2011

Powershell bondage, or at least binding.

I was out hunting around for best practices on writing Powershell commandlets and realized I was making one big mistake up front.  This article by Don Jones really explains the concept well.  I was really just writing functions, you don't have a commandlet until you have at least used cmdletbinding.  I think in the .Net world they call this "decorating" your function. 

For a straightforward example noting the two components in bold, when you use [CmdletBinding()] you can now use the -verbose switch, etc to add more value to your commandlets:

Function Get-CustomEventLog
              ,[string]$logName="Application", [string]$entryFilter="Error", [int]$rowsToReturn=25)

  Write-Verbose "Calling Get-EventLog for $computerName with $rowsToReturn rows"
  if($rowsToReturn -gt 100) {Write-Warning("Large Rowset potentially coming back");}
  Get-EventLog -ComputerName $computerName -LogName $logName -EntryType $entryFilter -Newest $rowsToReturn

Get-CustomEventLog -computerName "Consulting" -rowsToReturn 125 -verbose

Thursday, May 5, 2011

Finding all the databases with an owner other than SA

 Just an odd tidbit - If you use a standard DB owner and want to check to see that all databases are setup properly - you can use this Powershell script to find any DB that is not properly "owned".  Easy to change if you are searching for a specific owner by changing Where{$_.owner -ne "sa"} .  Enjoy!

$logFile = "C:\Powershell\Files\DBOwner_NonCompliant.txt"
if(Test-Path $logFile) {Remove-Item $logFile}

$server_list = @("Server\Merlot","Server\Cabernet")

foreach($sqlInstance in $server_list)
    $sqlInstance  | Out-File $logFile -Append

    $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
    $sqlServer.Databases | Where{$_.owner -ne "sa"} | Select-Object name, owner,LastBackupDate,Size |
        Out-File $logFile -Append


Invoke-Item $logFile