Powershell Script to check for low drive space

Just what it says. This will email you when your drive space gets below the desired threshold.


function sendMail($srv, $DriveName, $free) {
	$subject = "Low Drive Space on $srv"
	$body = "<p style='font-family: calibri;'>The <B>$DriveName</B> on server <B>$srv</B> has only <B>$free % </B> free.<BR/>
	Please try to free up some space so it is above $percentThreshold% free space.
	</p>"
	
	# Init Mail address objects
	$emailFrom = New-Object system.net.Mail.MailAddress "donotreply@yourcomany.com" , "Low Drive Space on $srv"
	$emailTo = New-Object system.net.Mail.MailAddress $alertEmail , "Low Drive Space"
	$smtp = new-object Net.Mail.SmtpClient($mailServer)
	$MailMessage = new-object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body)
	$MailMessage.IsBodyHtml = $true
	
	$smtp.Send($MailMessage)
}

function CheckDriveSpace($servername)
{
	$drives = gwmi win32_logicaldisk -filter "drivetype=3" -ComputerName ($servername) -ErrorAction SilentlyContinue
	foreach($drive in $drives)
	{
		$PercentFree = [int]([Int64]$drive.FreeSpace / [Int64]($drive.Size) * 100) 
		
		#SaveDriveinfo $ServerName $Drive.DeviceID $Drive.FreeSpace $drive.Size $PercentFree
		
		if($PercentFree -lt $percentThreshold)
		{	
			sendMail $servername $drive.DeviceID $PercentFree
		}
	}
}

function Main()
{
	try
	{
		$servers = Get-Content("C:\Servers.txt")
		foreach($server in $servers)
		{
			CheckDriveSpace $server
		}
		return 0;
	}
	catch
	{
		# Handle the error
		$err = $_.Exception
		write-host $err.Message
		while( $err.InnerException ) 
		{
			$err = $err.InnerException
			write-output $err.Message
		}
		return 1;
	}
}


Clear-Host
$percentThreshold = 15
$alertEmail = "dba@yourcompany.com"
$mailServer = "mail.yourcompany.com"
Main

Append SQL Files into one File with PowerShell

Here’s a quick script to append all *.sql files into one file. This automagically appends a ‘GO’ statement between the files as a just in case. One issue (that I’m currently working on) is that this currently scripts the files by the name of the file. If the files have to be run in a certain order and has the order number in the file name (eg, 1_CreateTables.sql, 2_AddData.sql, etc…) it will mess the order up if there are more than 9 files. For example, if there were over 10 files, the order would be 1_CreateFiles.sql, 10_AddProcedures.sql. I’m still working on correcting this.

This will create a file called out.sql in the originating directory that contains all the contents of the files.

param(
	#[string]$path = $(throw "You must enter the path to the .sql files you want to append."),
	[string]$NewFileName = "Out.sql"
)

$path = "\\rbnnorfasp01p2\RCDDev1\Pipeline\Database\20111015\SQL Scripts\MSA Scripts\02_MSA_SProcScripts"
$outFile = "$path\$NewFileName"

cls
if((Test-Path $outFile) -eq $true) {Remove-Item -Path $outFile -Force}

$files = Get-ChildItem -LiteralPath $path -Include "*.sql" -Recurse | Sort-Object -Property Name

New-Item -ItemType file -Path $outFile -Force | Out-Null

foreach($file in $files)
{
	Write-Host "Appending file $file..." -ForegroundColor Gray
	$content = Get-Content -Path $file.FullName
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile "--		$File"
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile $content
	Add-Content -Path $outFile "GO`n"
}

Write-Host "Completed file $outFile" -ForegroundColor DarkGreen

Finding tables with no Primary Key Index in Powershell

Here’s a quick powershell script to list all tables with no primary key index in powershell. The servers.txt requires that you have the instance name, so if the server is a default instance, use SERVERNAME\DEFAULT as the instance name.

function FindTablesNoClustIdx([string]$ServerName)
{
	sl SQLSERVER:\SQL\$ServerName\DATABASES
	$databases = gci
	foreach($database in $databases)
	{
		$tables = $database.Tables
		foreach($table in $tables)
		{
			$hasPK = $false
			foreach($index in $table.Indexes)
			{
				if($index.IndexKeyType -eq [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey)
				{
					$hasPK = $true
				}
			}
			if($hasPK -eq $false)
			{
				Write-Host "Table $Table.Name in database $database.name on server $ServerName has no primary key index"
			}
		}
	}
}

$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

cls
$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
{
	FindTablesNoClustIdx $server
}