Las Vegas SQL Server User Group

Thanks, Jason and Stacia, for allowing me to present tonight!

Here are the materials from the presentation, including bonus favorite scripts/functions.

PowerShell: The Way of the DBA Dragon – presentation scripts and slide deck.

I used Start-Demo to play back the commands to avoid typing.

Free Space on all servers for all fixed drives? Done.

A while ago I posted on querying free disk space using WMI and batch.

I wouldn't query free disk space using batch anymore.  The PowerShell script I tweaked and currently use to query free disk space is displayed below.  The script truncates for readability – the rounding methods are commented out if that’s your preference:

# spaceused.ps1
# Lars-UT http://larsrasmussen.blogspot.com
(Get-Date -format 'yyyy-MM-dd hh:mm:ss').ToString() | Out-Default
$outData = @("")
$server = $args[0]
$dataFromServer = Get-WmiObject Win32_Volume -ComputerName $server | Select-Object SystemName,Label,Name,DriveLetter,DriveType,Capacity,Freespace

foreach ($currline in $dataFromServer) {
    if ((-not $currline.name.StartsWith("\\")) -and ($currline.Drivetype -ne 5)) {
        [float]$tempfloatcapacity = [math]::truncate($currline.Capacity/ 1000000000)
    #[float]$tempfloatcapacity = [math]::round(($currline.Capacity/ 1000000000),1)
    add-member -InputObject $currline -MemberType NoteProperty -name Capacity_in_GB -value "$tempfloatcapacity GB"
    
    [float]$tempfloat = ($currline.Freespace / 1000000) / ($currline.Capacity / 1000000)
        $temppercent = [math]::round(($tempfloat * 100),2)
        add-member -InputObject $currline -MemberType NoteProperty -name FreePercent -value "$temppercent %"
        
    [float]$tempfloatfreespace = [math]::truncate($currline.Freespace/ 1000000000)
    #[float]$tempfloatfreespace = [math]::round(($currline.Freespace/ 1000000000),1)
    add-member -InputObject $currline -MemberType NoteProperty -name Freespace_in_GB -value "$tempfloatfreespace GB"
        
    $outData = $outData + $currline
    }
}

$outData | Select-Object SystemName,Label,Name,Capacity_in_GB,Freespace_in_GB,FreePercent | sort-object -property Name | format-table -autosize

To run this against muliple servers just put the server names, one per line, in a text file and save that file. You'll then assign that file to a variable in PowerShell and loop through each line for each server:

PS C:\> $serverlist = Get-Content .\ServerNames-Prod.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }

Here’s the pretty output!  Yep, those are sub-second times for querying multiple servers.

Need to document this?  Use Start-Transcript.  I’ve seen auditors use it like so:

PS C:\> $strTimestamp =  [string](Get-Date -format "yyyy-MM-dd_hh-mm")
PS C:\> start-transcript .\ServerSpace-$strTimestamp.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }   
PS C:\> Stop-Transcript

Enjoy.

Shrinking Transaction Logs in PowerShell

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | out-null
$s = New-Object ("Microsoft.SqlServer.Management.SMO.Server") 'MYSQLSERVER'

#Current Transaction Log Size in MB
$s.databases["MyDB"].Logfiles[0].Size/1KB

$s.databases["MyDB"].LogFiles[0].Shrink(2, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::Default)

$s.databases["MyDB"].Logfiles.refresh($true)
$s.databases["MyDB"].Logfiles[0].Size/1KB