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
Be First to Comment