Check database auto-shrink setting with Powershell

It’s very well known that auto-shrink is bad for reasons I won’t repeat here. Perhaps you’ve been meaning to check all your servers and databases but simply haven’t got around to it? A simple bit of Powershell makes this into a trivial task;

?View Code POWERSHELL
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
 
# List of sql servers here
$sqlservers = @("server1", "server2", "server3", "server4", "server5", "server6");
 
foreach($sqlserver in $sqlservers)
{
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver;
	$databases = $srv.Databases;
	$count = 0;
	foreach($db in $databases)
	{	
		if($db.AutoShrink -eq $true)
		{
			Write-Host -ForegroundColor Red "Warning: $db on $sqlserver has auto-shrink enabled.";
			$count++;
		}
	}
	Write-Host "$sqlserver has $count databases set to auto-shrink.";
}

The script will output something like below;

Warning: [db1] on server1 has auto-shrink enabled.
server1 has 1 databases set to auto-shrink.
server2 has 0 databases set to auto-shrink.
server3 has 0 databases set to auto-shrink.
server4 has 0 databases set to auto-shrink.
server5 has 0 databases set to auto-shrink.
server6 has 0 databases set to auto-shrink.

Leave a Reply