Shrink all databases on SQL Server

This post is intended to be used as a hack for Developers to release some disk space. Please do not use on actual production environments.

Usually, the database transaction logs also takes a lot of space.

What I also usually do is set the Recovery Mode of all the local databases on Dev Machines to Simple instead of Full. That way the growth of log files is also reduced for future.

Next to free up the space (or shrink all databases), you can use the query below. What it will do is basically set the recovery mode to simple for all databases and then shrink them.

CREATE TABLE #DataBases (ID INT IDENTITY, Name NVARCHAR(100))

INSERT #DataBases
SELECT NAME FROM sys.databases WHERE NAME NOT IN ('master','model','msdb','tempdb')

DECLARE @Count INT = 1
DECLARE @NrOfDBs INT = 0

SELECT @NrOfDBs = COUNT(0) FROM #DataBases

DECLARE @DBName NVARCHAR(100), @SQL NVARCHAR(MAX)

WHILE (@Count < @NrOfDBs)
BEGIN
     SELECT @DBName = Name FROM #DataBases WHERE ID = @Count

     SELECT @SQL = 'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE'

     PRINT(@SQL)
     EXEC(@SQL)

     --Shrink Database
     DBCC SHRINKDATABASE (@DBName , 0)
     
     SET @Count = @Count + 1
END

DROP TABLE #DataBases