Monday, March 16, 2009

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