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
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
Comments
Post a Comment