-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE Northwind -- as a good test case...
go

DBCC LOGINFO
	--number of rows = VLFs

-- For Northwind - Not Bad... if you want to make it bad :)... Use "FillLog.sql"
-- to create a demo table and then insert lots of rows (in a single tran) into 
-- northwind. This will create a single LARGE transaction NOT allowing the
-- log to be cleared and also increasing the size of the transaction log 
-- significantly because of "auto grow." This is one of the primary reasons
-- that databases end up with a lot VLFs.

-- if you have more than 25 (a 1 GB Log should have only 8)
-- get rid of excessive vlfs

-- TO get rid of the excessive vlfs

--1) Backup or Clear you log
-- If regular backups then backup
	backup log dbname to device 

--If you're not doing reg backups (i.e. simple recovery model)
-- then clear (and WHY aren't you doing regular log backups?)
	BACKUP LOG dbname WITH NO_LOG
	BACKUP LOG dbname WITH TRUNCATE_ONLY
	
-- For example
	BACKUP LOG Northwind WITH TRUNCATE_ONLY

--2) Shrink the log to as small a size as possible (truncateonly)
-- sp_helpfile to see file list
	DBCC SHRINKFILE(tranloglogicalfilename, TRUNCATEONLY)

-- For example
	DBCC SHRINKFILE(Northwind_log, TRUNCATEONLY)

--3) Alter the database to modify the file prop to the appropriate 
--size
ALTER DATABASE dbname
	MODIFY FILE 
		( NAME = logical_file_name 
			 , SIZE = new_size)

-- Size default to MB but you can use KB, MB, GB, TB)
-- For example
ALTER DATABASE Northwind
	MODIFY FILE 
		( NAME = 'Northwind_log'
			 , SIZE = 20)

-- Use the following to verify file sizes
EXEC sp_helpfile

-- Finally, check your VLFs again. You might still have 10-20 
-- but at least you won't have hundreds.

DBCC LOGINFO
	--number of rows = VLFs

-- To keep this from happening again...
	-- Regularly backup the log (to keep it small)
	-- Adjust the growth rate so that it's more reasonable (not so small)
	-- Monitor the log space