Sql Server Powershell

Turn off AutoShrink on databases via powershell

,

Easy peasy script to turn off AutoShrink on all databases.  I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}

Responses

  1. Chris Harris Avatar
    Chris Harris

    This is a great idea but the script doesn’t appear to work:

    You must provide a value expression on the right-hand side of the ‘-‘ operator.
    At C:\scripts\Turnoffautoshrink.ps1:8 char:69
    + $srv.Databases | where{-not $_.IsSystemObject -and $_.Name – <<<< notin $ignoreDBs} | %{
    + CategoryInfo : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : ExpectedValueExpression

    Reply
    1. Scott Newman Avatar
      Scott Newman

      Methinks you’re using a powershell version 1 or 2. -notin only works in version 3 and above.

      Scott

      Reply

Leave a reply to Scott Newman Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.