Database Log files Shrinking DBCC SHRINKFILE

Shrinking database log file is necessary if log has grown out of control whether after a one-time data delete or where recovery model of the database is FULL and no log backups have been carried out. To reverse the effect of a one-time operation that caused unexpected log file growth are convincing reasons for shrinking a database\ database file.

Shrinking database is not a recommended process and should not be part of any regular maintenance operation but in any case you need to do so, you must REBUILD\ REORGANIZE indexes of database along with statistics update.

Script to shrink log files of all user created databases on server where some are with Simple'' and some are with 'Full' recovery model

	/*
	Script By: Amna Asif for http://sqlcache.blogspot.com/
	Purpose: To shrink Database Log Files
	*/
	Use Master
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ARITHABORT ON
	GO
	DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)
	DECLARE DatabaseList CURSOR
	FOR
	SELECT name,recovery_model_desc
	FROM sys.databases
	WHERE state_desc = 'ONLINE'
	AND is_read_only = 0
	and database_id>4
	ORDER BY name
	OPEN DatabaseList
	FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')
	If @DBRecoveryDesc='Full'
	Begin
	Print('Use '+@DBName+'
	GO
	ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
	GO
	DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
	GO
	ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH NO_WAIT
	GO ')
	Print '----------------------------------------------------------- '
	END
	If @DBRecoveryDesc='Simple'
	Begin
	Print('Use '+@DBName+'
	GO
	DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
	GO
	')
	Print '----------------------------------------------------------- '
	END
	FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
	END
	CLOSE DatabaseList
	DEALLOCATE DatabaseList

view raw dbcc_shrinkfile.sql hosted with ❤ by GitHub