Detecting and reducing VLFs in SQL Server 2008 Glenn Berry June 2010 http://glennberrysqlperformance.spaces.live.com/ Twitter: GlennAlanBerry https://sqlserverperformance.wordpress.com/2010/06/22/detecting-and-reducing-vlfs-in-sql-server-20082008-r2-transaction-log-files/

Detecting and reducing VLFs in SQL Server 2008.sql

-- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

--select db_name(), * from sys.database_files

declare	@DbName		sysname		= db_name()
		    ,@Dt			varchar(30)	= convert(varchar(30), getdate(), 121)
		    /*, @rn varchar(3) = cast(cast(((999 + 1) - 100) * rand(checksum(newid())) + 100 as int) as varchar)*/
		    ,@TempName	sysname
		    ,@OutPath		varchar(max)	= ''
		    ,@DeSc		varchar(512)

--replace the chars in the string so we can use it as part of the file name
set @Dt = replace(replace(replace(replace(@Dt, ':', ''), ' ', '_'), '-', ''), '.', '_')

if len(isnull(@OutPath, '')) > 0
	and right(@OutPath, 1) <> '\'
	set @OutPath = @OutPath + '\'

--use a temp name for the dbname	
set @TempName = +@DbName + '_' + @Dt /*+ '_' + @rn */

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;


if exists (
		select
			1
		from
			sys.databases
		where
			Name = db_name()
			and recovery_model = 1
	)
begin
	select
		@OutPath = @OutPath + @DbName + '_full_db.bak'
		,@DeSc = @TempName + ' - Transaction Log Backup'
	backup log @DbName to disk = @OutPath with noformat, init,
	name = @DeSc, skip, norewind, nounload, stats = 1;
end
else
begin
	select
		@OutPath = @OutPath + @DbName + '_full_db.bak'
		,@DeSc = @TempName + ' - Full DB Backup'
	print @OutPath
	backup database @DbName to disk = @OutPath
	with name = @DeSc, retaindays = 5, noinit, stats = 10;
end

go

declare @TempName sysname

select
	@TempName = Name
from
	sys.database_files
where
	type = 1

-- Step 2: Shrink the log file
dbcc shrinkfile (@TempName, 0, truncateonly);
go

-- Check VLF Count for current database
DBCC LogInfo;