Shrink all databases on a Microsoft SQL Server instance

When disk free space on your Windows Server goes below your threshold and you discover, that your MSSQL installations DATA directory is the biggest one with lot’s of large log files (.LDF extension), then this solution is for you.

This very simple command:

EXEC sp_MSForEachDB 'select ''?'' as [Database]; ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE (''?'' , 0)'

get’s all databases, and for each:

1. Displays the database name
2. Changes database recovery model to Simple (Important! Read this information http://technet.microsoft.com/en-us/library/ms189275.aspx for implications when changing Recovery Model)
3. Shrinks all database files, leaving 0% free space inside database and log files

This SQL Management Studio screenshot shows the output of the command:

In my case 14GB DATA directory was reduced to 2GB. Impressive.