PowerShell script to shrink & rebuild your SQL Server databases.

Just change the DB1, DB2, DB3, etc.. to be the actual databases you plan on rebuilding.
Same with the ServerName\InstanceName.

Caveat: Don’t run this script on a regular basis. If your databases keep growing in size, there’s usually a reason for it.

$databases = @('DB1','DB2','DB3')
$watch = New-Object System.Diagnostics.Stopwatch
foreach ($database in $databases) {
 $watch.Start()
 Write-Host "Shrinking - " $database
 Invoke-DbaDbShrink -SqlInstance "ServerName\InstanceName" -Database $database -PercentFreeSpace 10 -FileType Data -StepSize 1GB
 Write-Host $time " done " $database
 Write-Host "Rebuilding " $database
 Invoke-DbaQuery -SqlInstance "ServerName\InstanceName" -Database $database -Query "Exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'"
 $time = $watch.Elapsed.TotalMinutes
 $watch.reset()
 Write-Host $time " - " $database
}

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 )

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