This stored procedure can be called every day to free disk space
2008-11-20 (first published: 2008-10-06)
958 reads
This stored procedure can be called every day to free disk space
USE master GO /* EXEC PS_SHRINK_ALL_USER_DATABASES */ IF EXISTS(SELECT TOP 1 1 FROM sys.procedures WHERE name = 'PS_SHRINK_ALL_USER_DATABASES') DROP PROC PS_SHRINK_ALL_USER_DATABASES GO CREATE PROC PS_SHRINK_ALL_USER_DATABASES AS BEGIN DECLARE @DBs TABLE(database_id NVARCHAR(max),name NVARCHAR(max)) INSERT @DBs SELECT database_id,name FROM sys.databases WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') DECLARE @DB_ID NVARCHAR(MAX) , @DB_NAME NVARCHAR(MAX), @LOG_FILENAME NVARCHAR(MAX), @DATA_FILENAME NVARCHAR(MAX) WHILE EXISTS(SELECT TOP 1 1 FROM @DBs) BEGIN SET @DB_ID = (SELECT TOP 1 database_id FROM @DBs) SET @DB_NAME = (SELECT TOP 1 name FROM @DBs) SET @DATA_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID AND type = 0) SET @LOG_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID AND type = 1) EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE') EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)') EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)') EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL') DELETE @DBs WHERE database_id = @DB_ID END END GO EXEC PS_SHRINK_ALL_USER_DATABASES GO