/*============================================================================
  File:     VLF Optimization.sql

  Summary:  This script shows the process for removing excessive VLFs within 
			a database's transaction logs.

  Date:     November 2005

  Version:	SQL Server 2005 RTM
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For additional scripts and sample code:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


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