-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 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.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


DBCC LOGINFO
	--number of rows = 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 then clear 
-- (why aren't you doing regular log backups?)
	BACKUP LOG dbname WITH NO_LOG
	BACKUP LOG dbname WITH TRUNCATE_ONLY

--2) Shrink the log to as small a size as possible (truncateonly)
-- Use sp_helpfile to see file list (and the Transaction Log's Logical File Name)
EXEC sp_helpfile 
DBCC SHRINKFILE(tranloglogicalfilename, 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