List db size on remote server with powershell

Today, I wanted to create a powershell script to list all databases on a remote SQL Server and their total size in MB. I haven’t made the script as robust as needed, but this should be a nice first version. The script will connect to the server given in parameter $server. I succesfully connected, the script will calculate total size of all databases, and print a header, before it lists all database with it’s size in MB.

Param ($Server = "localhost")

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') `
		-argumentlist $Server

$dataTotal = $SMOserver.Databases | Measure-Object -property Size -sum

write-host "********************************************"
Write-host "Connected to server '$server'"
write-host "  DB Count    = " $dataTotal.count
write-host "  Total size  = " $dataTotal.sum.ToString("#,##0") "MB"
write-host "********************************************"
$x2 = @{label="Size (MB)";Expression={$_.Size};FormatString="#,###,##0"}

$SMOserver.Databases `
	| select name, size `
	| sort-object Size -descending `
	| Format-Table Name, $x2 -auto
Advertisements

Check eventlog status in powershell

When we monitor servers, we need to get notified if there are new errors and/or warnings in the Windows Eventlog.. Normally, it is the Application event log we check for these errors and warnings. The required functionality is that the script should check for errors and warnings on the current machine and if the number of event exceeds a maximum, the script should send an email to administrator to notify that some further investigation is needed on the server.

Here is the second powershell script I wrote today:

CHECK_EVENT_LOG_STATISTICS.PS1:

param([String]$logname="Application", [Int]$maxEventCount=10)

$since = ((get-date).addDays(-1));

$errwarnlist =  Get-Eventlog -logname $logname -after $since 
			-EntryType Error, Warning
$linecount = $errwarnlist | Measure-Object –Line

$msg = $env:COMPUTERNAME + " " + $logname + " eventlog has " 
       + $linecount.Lines  
       + " errors/warnings since " + $since.ToString("dd.MM.yyy HH:mm") 
       + ". Limit = " + $maxEventCount;

if ($linecount.Lines -gt $maxEventCount)
{
    $xto = john.doe@company.com
    $xfrom = "no-reply@company.com"
    $xsub = $env:COMPUTERNAME + ": " + $logname 
            + " eventlog exceeds limit for errors/warnings";

    Write-host $msg

    Send-MailMessage -To $xto -From $xfrom  
                 -Subject $xsub  -Body $msg 
                 -SmtpServer smtp.company.com
}
else
{
    Write-host $msg
}