/*
Author: Leonardo Pasta

Version: 1.0

Date: 02/09/2008

Supports: SQL2008

Sintax: sp_DBA_DefragLog { @Action=['Report'|'Simulate'|'Fix'] } {, @TargetMb = value}

Summary: Report and fix Transaction Log's VLF fragmentation.
         Based on Kimberly Tripp's article: http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=934f3755-5b1d-4572-a386-c6a2a0d14a9e
*/
USE master
GO
IF OBJECT_ID('sp_DBA_DefragLog') IS NOT NULL
  DROP PROCEDURE sp_DBA_DefragLog
GO
CREATE PROCEDURE sp_DBA_DefragLog @Action varchar(10) = 'Report', @TargetMb int=NULL
AS
BEGIN
	SET NOCOUNT ON
	DECLARE  @Sql varchar(300)
			,@LogFileName sysname
			,@Size int
			,@Step int
			,@Loop tinyint
			,@MaxIncrement float
			,@FragLimit smallint
         
	SET @MaxIncrement = 8192   --Change to what you consider a reasonable maximum growth step    
	SET @FragLimit = 100       --Maximum number of fragments deemed acceptable
  
--DBCC LOGINFO requires sysadmin permissions
	IF IS_SRVROLEMEMBER('sysadmin') = 0
	BEGIN
		RAISERROR('This procedure requires sysadmin priviledges to run',11,1)
		RETURN 1
	END
  
	CREATE TABLE #VlfDetail
	(
		 [FileId] tinyint NOT NULL
		,[FileSize] int NOT NULL
		,[StartOffset] int NOT NULL
		,[FSeqNo] smallint NOT NULL
		,[Status] int NOT NULL
		,[Parity] int NOT NULL
		,[CreateLSN] varchar(20) NOT NULL	
	)
	
	--The DBCC below output one row for each log fragment (VLF)
	INSERT INTO #VlfDetail ([FileId], [FileSize], [StartOffset], [FSeqNo], [Status], [Parity], [CreateLSN])
	EXEC('DBCC LOGINFO() WITH TABLERESULTS, NO_INFOMSGS')
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Error collecting Transaction Log fragmentatio info', 11, 2)
		RETURN 2
	END
	
	IF UPPER(@Action) = 'REPORT'
		SELECT   [NoLogFragments] = COUNT(*)
				,[AvgFragSize (Mb)] = AVG([FileSize]) / 1048576.0 
				,[Report] = CASE WHEN COUNT(*) > @FragLimit THEN 'Consider running ''Fix'' to reduce the number of fragments and increase performance.'
								 ELSE 'Log fragmentation is low.'
							END     
		FROM #VlfDetail
	ELSE IF UPPER(@Action) IN ('SIMULATE','FIX')  
	BEGIN
		/*  The select below ended up more complicated than I expected because I decided to deal 
		  with databases with more than one transaction log, even tough no one should be doing 
		  this. In this case the @TargetMb (if provided) will be proportionally divided for all 
		  files. The end total might be slightly higher due to rounding.
		*/
		DECLARE c_LogFiles CURSOR FOR
		SELECT name, ROUND(ISNULL( (@TargetMb * CAST(SUM([FileSize]) AS float) / (SELECT SUM([FileSize]) FROM #VlfDetail))
	                            ,SUM([FileSize]) / 1048576.0)
	                       ,0) 
	    FROM #VlfDetail v
	    JOIN sys.database_files d ON v.[FileId] = d.file_id
        GROUP BY name
      
		OPEN c_LogFiles
      
		FETCH c_LogFiles INTO @LogFileName, @Size 
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			--Reduce the Transaction Log to its minimum size			
			SET @Sql = 'CHECKPOINT; DBCC SHRINKFILE('+@LogFileName+',TRUNCATEONLY) WITH NO_INFOMSGS;'      
			IF UPPER(@Action) = 'SIMULATE'
  				PRINT @Sql
  			ELSE
  			BEGIN
  				EXEC(@Sql)
  	      		IF @@ERROR <> 0
				BEGIN
					RAISERROR('Error shrinking Transaction Log.', 11, 3)
					RETURN 3
				END 
  			END  

		--Caculate the optimal growth (an constant value as close as possible to the defined threshold)
			SELECT @Step = ROUND(@Size / CEILING(@Size / @MaxIncrement),0) 
				  ,@Loop = CEILING(@Size / @MaxIncrement)     
				  ,@Size = @Step
			WHILE @Loop > 0
			BEGIN
				SET @Sql = 'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE (name ='''+@LogFileName+''', size='+CAST(@Size as varchar(20))+')'
  				IF UPPER(@Action) = 'SIMULATE'
  					PRINT @Sql
  				ELSE
  					EXEC(@Sql) 
  	      		IF @@ERROR <> 0
				BEGIN
					RAISERROR('Error expanding Transaction Log to desired size', 11, 5)
					RETURN 5
				END 
  	         
  				SELECT @Size = @Size + @Step
  					  ,@Loop = @Loop - 1
  			END  
			FETCH c_LogFiles INTO @LogFileName, @Size	    
		END
		CLOSE c_LogFiles
		DEALLOCATE c_LogFiles  
	END
	ELSE
	BEGIN
		RAISERROR('Invalid @Action parameter. Possible actions are ''Report'', ''Simulate'' or ''Fix''',11,3)
		RETURN 3
	END      
END
GO
IF OBJECT_ID('sp_DBA_DefragLog') IS NOT NULL --i.e. Compiled sucessfully
	EXEC sp_MS_marksystemobject 'sp_DBA_DefragLog'
GO