Shrink All Databases on SQL Server

This post Shrink all databases on SQL Server written by Manas Bhardwaj appeared first on Manas Bhardwaj's Stream.

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 take 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

This post Shrink all databases on SQL Server written by Manas Bhardwaj appeared first on Manas Bhardwaj's Stream.