/*============================================================================
  File:     VLF Optimization - Northwind Walkthrough.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.
============================================================================*/

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

sp_helpfile
go

DBCC LOGINFO
	--number of rows = VLFs

-- For 4-8-12 - not too bad... 
-- Now, if you want to make it bad... Use "Fill_Log.sql" to create a 
-- demo table and then insert lots of rows (in a single tran) into 
-- northwind. This LARGE transaction will NOT allow the log to be cleared 
-- and also increased 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?)
	CHECKPOINT
	
--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)

-- This should return output where the CurrentSize and MinimumSize
-- are equal in value. If they are not, then you might have pending
-- transactions. The best way to solve this is to run this at "off"
-- hours and/or kick all users out of the database. You should be
-- able to kick users out, shrink/grow the file and then let users
-- back in within a relatively short amount of time (depending on 
-- length of time it takes to alter the file and extend it).

--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 = 100)

-- The number of VLFs added will be based on the size of the "chunk"
--		0 < 64MB =  4 VLFs
--   64MB <  1GB =  8 VLFs
--		  >	 1GB = 16 VLFs

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