Technical Article

Shrinking all log files on SQL Server 2008R2

,

The script can be use as it is. The code can be put even in a stored procedure without arguments and used in a job. It will shrink all LOG files for the databases created by users.

/*
The script is designed to work on a sql server 2008R2. However it works on 2005 and sql server 2012 instances. 
It shrinks all log files for the databases created by users
Written by Igor Micev, 2012
*/
	declare @logname nvarchar(128)
	declare @dbname nvarchar(128)
	declare @dynamic_command nvarchar(1024)
	set @dynamic_command = null
	declare log_cursor cursor for
	select db_name(mf.database_id),name 
	from sys.master_files mf
	where mf.database_id not in (1,2,3,4) --avoid system databases
	and mf.name not like 'ReportServer$%' 	
	and right(mf.physical_name,4) = '.ldf' and mf.state_desc='online'
	open log_cursor
	fetch next from log_cursor into @dbname,@logname
	while @@fetch_status = 0
	begin
		set @dynamic_command = 'USE '+@dbname+' DBCC SHRINKFILE(N'''+@logname+''',0,TRUNCATEONLY)'	
		exec sp_executesql @dynamic_command
		fetch next from log_cursor into @dbname,@logname
		set @dynamic_command = null
	end
	close log_cursor
	deallocate log_cursor

Rate

3.17 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

3.17 (6)

You rated this post out of 5. Change rating