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{

}

2 thoughts on “Turn off AutoShrink on databases via powershell

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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