Friday, October 2, 2009

Shrink Databases using sp_MSForEachDB

Using undocumented stored procedure sp_MSForEachDB and DBCC SHRINKDATABASE command you can shrink all databases on an SQL Server in one line of code

exec sp_MSForEachDB 'if ''?'' not in (select name from sys.databases where is_read_only = 1) DBCC SHRINKDATABASE([?]) '

You may need to add additional checks [skip offline dbs for example]. Test on SQL Server 2005.