Compact databases to increase disk space on dev VMs

login to SQL Server management studio with a user that has admin rights, and run the following script -




DECLARE @DB VARCHAR(255)

DECLARE C CURSOR FOR

SELECT NAME FROM SYS.DATABASES WHERE IS_READ_ONLY=0 AND STATE=0

AND NAME NOT IN ('master','model','tempdb','msdb')

OPEN C

FETCH C INTO @DB

WHILE @@FETCH_STATUS=0

BEGIN

EXEC SP_DBOPTION @DB,'trunc. log on chkpt.','true'

DBCC SHRINKDATABASE (@DB)

FETCH NEXT FROM C INTO @DB

END

CLOSE C



DEALLOCATE C