SET NOCOUNT ON; USE [Tempdb] GO --Dropping temp tables if exist IF OBJECT_ID('tempdb..#databases') IS NOT NULL BEGIN DROP TABLE #databases END IF OBJECT_ID('tempdb..#dbccloginfo') IS NOT NULL BEGIN DROP TABLE #dbccloginfo END IF OBJECT_ID('tempdb..#dbccloginfo2012') IS NOT NULL BEGIN DROP TABLE #dbccloginfo2012 END IF OBJECT_ID('tempdb..#vlfcounts') IS NOT NULL BEGIN DROP TABLE #vlfcounts END IF OBJECT_ID('tempdb..#TmpLogspace') IS NOT NULL BEGIN DROP TABLE #TmpLogspace END --For multiple log files IF OBJECT_ID('tempdb..#DatabaseFiles') IS NOT NULL BEGIN DROP TABLE #DatabaseFiles END --Exit if it is SQL2000 DECLARE @MajorVersion TINYINT SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1) IF @MajorVersion <= 8 -- pre-SQL2000 BEGIN RETURN END /* Build data file info */ CREATE TABLE #DatabaseFiles ( [database_name] [sysname] NOT NULL , [file_id] [int] NOT NULL , [file_guid] [uniqueidentifier] NULL , [type] [TINYINT] NOT NULL , [type_desc] [nvarchar](60) NULL , [data_space_id] [int] NOT NULL , [name] [sysname] NOT NULL , [physical_name] [nvarchar](260) NOT NULL , [state] [TINYINT] NULL , [state_desc] [nvarchar](60) NULL , [size] [int] NOT NULL , [max_size] [int] NOT NULL , [growth] [int] NOT NULL , [is_media_read_only] [bit] NOT NULL , [is_read_only] [bit] NOT NULL , [is_sparse] [bit] NOT NULL , [is_percent_growth] [bit] NOT NULL , [is_name_reserved] [bit] NOT NULL , [create_lsn] [numeric](25, 0) NULL , [drop_lsn] [numeric](25, 0) NULL , [read_only_lsn] [numeric](25, 0) NULL , [read_write_lsn] [numeric](25, 0) NULL , [differential_base_lsn] [numeric](25, 0) NULL , [differential_base_guid] [uniqueidentifier] NULL , [differential_base_time] [datetime] NULL , [redo_start_lsn] [numeric](25, 0) NULL , [redo_start_fork_guid] [uniqueidentifier] NULL , [redo_target_lsn] [numeric](25, 0) NULL , [redo_target_fork_guid] [uniqueidentifier] NULL , [backup_lsn] [numeric](25, 0) NULL ) EXEC dbo.sp_MSforeachdb 'INSERT INTO #DatabaseFiles SELECT ''[?]'' AS database_name, * FROM [?].sys.database_files Where type =1' --variables to hold each 'iteration' DECLARE @query NVARCHAR(1000) DECLARE @dbName SYSNAME DECLARE @vlfs INT DECLARE @logFileName NVARCHAR(200) --table variable used to 'loop' over databases CREATE TABLE #databases (dbname sysname) INSERT INTO #databases --only choose online databases SELECT name FROM sys.databases WHERE STATE = 0 --table variable to hold results CREATE TABLE #vlfcounts ( dbName SYSNAME , vlfCount INT , tLogSizeMB INT , logFileName NVARCHAR(200) ) --table variable to capture DBCC loginfo output --changes in the output of DBCC loginfo FROM SQL2012 mean we have to determine the version IF @MajorVersion < 11 -- pre-SQL2012 BEGIN CREATE TABLE #dbccloginfo ( fileid TINYINT , file_size BIGINT , start_offSET BIGINT , fseqno INT , [status] TINYINT , parity TINYINT , create_lsn NUMERIC(25, 0) ) WHILE EXISTS ( SELECT TOP 1 dbname FROM #databases ) BEGIN SET @dbName = ( SELECT TOP 1 dbname FROM #databases ) SET @query = 'dbcc loginfo (' + '''' + @dbName + ''') WITH NO_INFOMSGS' INSERT INTO #dbccloginfo EXEC ( @query ) SET @vlfs = @@rowcount SET @query = 'use [' + @dbName + ']' + CHAR(10) + 'Select @logfilename = name FROM sys.database_files Where type =1' EXEC sp_executesql @query, N'@logFileName varchar(200) OUTPUT', @logFileName OUTPUT INSERT #vlfcounts VALUES ( @dbName, @vlfs, 0, @logfileName ) DELETE FROM #databases WHERE dbname = @dbName END --while END ELSE BEGIN CREATE TABLE #dbccloginfo2012 ( RecoveryUnitId INT , fileid TINYINT , file_size BIGINT , start_offSET BIGINT , fseqno INT , [status] TINYINT , parity TINYINT , create_lsn NUMERIC(25, 0) ) WHILE EXISTS ( SELECT TOP 1 dbname FROM #databases ) BEGIN SET @dbName = ( SELECT TOP 1 dbname FROM #databases ) SET @query = 'dbcc loginfo (' + '''' + @dbName + ''') WITH NO_INFOMSGS ' INSERT INTO #dbccloginfo2012 EXEC ( @query ) SET @vlfs = @@rowcount SET @query = 'use [' + @dbName + ']' + CHAR(10) + 'Select @logfilename = name FROM sys.database_files Where type =1' EXEC sp_executesql @query, N'@logFileName varchar(200) OUTPUT', @logFileName OUTPUT INSERT #vlfcounts VALUES ( @dbName, @vlfs, 0, @logfileName ) DELETE FROM #databases WHERE dbname = @dbName END --while END CREATE TABLE #TmpLogspace ( DBName NVARCHAR(150) , LOGSIZE_MB DECIMAL(18, 0) , LOGSPACE_USED DECIMAL(18, 1) , LOGSTATUS DECIMAL(18, 0) ) INSERT #TmpLogspace ( DBName , LOGSIZE_MB , LOGSPACE_USED , LOGSTATUS ) EXEC ( 'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;' ) ALTER TABLE #TmpLogspace ADD SQLSrvrName NVARCHAR(100), InstName NVARCHAR(100) UPDATE #TmpLogspace SET SQLSrvrName = CAST(SERVERPROPERTY('MachineName') as NVARCHAR(100)) ,InstName = ISNULL(CAST(SERVERPROPERTY('InstanceName') as VARCHAR(100)),'DEFAULT') UPDATE vlf SET tLogSizeMB = tlog.LOGSIZE_MB FROM #vlfcounts vlf inner join #TmpLogspace tlog on vlf.dbname = tlog.DBName --output the full list --output the full list SELECT CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100)) AS SQLSrvrName , ISNULL(CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(100)), 'DEFAULT') AS InstName , dbname , vlfcount , tLogSizeMB , logfileName FROM #vlfcounts WHERE ( ( vlfcount > 50 AND tLogSizeMB <= 8 * 1024 ) OR ( vlfcount > 200 AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024 ) OR ( vlfcount > 300 AND tLogSizeMB BETWEEN 36 * 1024 + 1 AND 72 * 1024 ) OR ( vlfcount > 400 AND tLogSizeMB BETWEEN 72 * 1024 + 1 AND 108 * 1024 ) OR ( vlfcount > 500 AND tLogSizeMB >= 108 * 1024 + 1 ) OR ( vlfCount < CEILING(tLogSizeMB / 512) )--Looking for too few VLF ) AND dbname NOT IN ( 'tempdb', 'model' ) ORDER BY vlfCount DESC DECLARE @databaseName NVARCHAR(200) DECLARE @vlfCount NVARCHAR(200) DECLARE @currentLogSize NVARCHAR(200) DECLARE @newLogSize INT DECLARE @physicalName NVARCHAR(200) DECLARE @totalSize NVARCHAR(200) DECLARE @increment INT DECLARE @initialFileSize int DECLARE @autoGrowth INT DECLARE dbcrsr CURSOR FOR SELECT dbname, vlfCount, tLogSizeMB FROM #vlfcounts WHERE ( ( vlfcount > 50 AND tLogSizeMB <= 8 * 1024 ) OR ( vlfcount > 200 AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024 ) OR ( vlfcount > 300 AND tLogSizeMB BETWEEN 36 * 1024 + 1 AND 72 * 1024 ) OR ( vlfcount > 400 AND tLogSizeMB BETWEEN 72 * 1024 + 1 AND 108 * 1024 ) OR ( vlfcount > 500 AND tLogSizeMB >= 108 * 1024 + 1 ) OR ( vlfCount < CEILING(tLogSizeMB / 512) )--Looking for too few VLF ) AND dbname NOT IN ( 'tempdb', 'model' ) ORDER BY vlfCount DESC OPEN dbcrsr FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize WHILE @@fetch_status = 0 BEGIN --need for multiple log file DECLARE filecrsr CURSOR FOR SELECT name, size/128 FROM #DatabaseFiles Where database_name = '['+@databaseName + ']' and type_desc='LOG' OPEN filecrsr FETCH NEXT FROM filecrsr INTO @physicalname, @totalSize WHILE @@fetch_status = 0 BEGIN /* Based on the bug mentioned by Paul Randal in his blog I am setting these depending on the sql version http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/ In this section you can change how you want to set the intial size, autogrowth and new size for log file. */ IF @MajorVersion < 11 --Up to SQL2008R2 BEGIN SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) ) / 1000 )) * 1000 IF ( CONVERT(INT, @newLogSize) < 4001 )--less than 4gb BEGIN SELECT @initialFileSize = 1000 SELECT @autoGrowth = 1000 SELECT @increment = 1000 END ELSE IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4001 AND 16000 ) BEGIN SELECT @initialFileSize = 4000 SELECT @autoGrowth = 1000 SELECT @increment = 1000 END ELSE IF ( CONVERT(INT, @newLogSize) > 16000 ) BEGIN SELECT @initialFileSize = 4000 SELECT @autoGrowth = 4000 SELECT @increment = 4000 END END ELSE IF (@MajorVersion =11)--SQL2012 BEGIN SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) ) / 1024 )) * 1024 IF ( CONVERT(INT, @newLogSize) < 4096 )--less than 4gb BEGIN SELECT @initialFileSize = 1024 SELECT @autoGrowth = 1024 SELECT @increment = 1024 END ELSE IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4097 AND 16384 ) BEGIN SELECT @initialFileSize = 4096 SELECT @autoGrowth = 1024 SELECT @increment = 1024 END ELSE IF ( CONVERT(INT, @newLogSize) > 16384 ) BEGIN SELECT @initialFileSize = 4096 SELECT @autoGrowth = 4096 SELECT @increment = 4096 END END ELSE --SQL2014 --Keeping autogrowth and increment at low size becuase of the new VLF rule in SQL2014 BEGIN SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) ) / 1024 )) * 1024 IF ( CONVERT(INT, @newLogSize) < 4096 )--less than 4gb BEGIN SELECT @initialFileSize = 1024 SELECT @autoGrowth = 1024 SELECT @increment = 1024 END ELSE IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4097 AND 16384 ) BEGIN SELECT @initialFileSize = 4096 SELECT @autoGrowth = 512 SELECT @increment = 512 END ELSE IF ( CONVERT(INT, @newLogSize) > 16384 ) BEGIN SELECT @initialFileSize = 4096 SELECT @autoGrowth = 512 SELECT @increment = 512 END END PRINT '/****************************************************' PRINT '--Fixing database : '+ @databaseName PRINT '--Current log Size : ' + @currentLogSize PRINT '--Current VLF Count : ' + @vlfCount PRINT '--Log file Name : ' + @physicalname PRINT '--Setting New Size to: ' + CONVERT (NVARCHAR (20) ,@newLogSize) PRINT CHAR(10) + '-- Step 1: Take transaction log backup if needed, and then shrink the log file' + CHAR(10) + 'USE [' + @databaseName + ']' +CHAR(10)+'GO'+CHAR(10)+'DBCC SHRINKFILE (N''' + @physicalname + ''' , 0, TRUNCATEONLY);' PRINT CHAR(10) + '--Step 2 : Check your vlf count now using DBCC LOGINFO, check your log file size. If size or VLF count still high take log backup, check long running transaction and shrink again.' +CHAR(10) + '--Once you have reasonable size and count move to step 3' PRINT CHAR(10)+'--Step 3: Grow log file back to original size and adjust autogrowth so VLF count will not grow high in future.' PRINT 'USE [MASTER];' + CHAR(10) + 'GO' PRINT 'ALTER DATABASE [' + @databaseName + '] MODIFY FILE (NAME = N''' + @physicalname + ''', SIZE = ' + CONVERT (VARCHAR (20),@initialFileSize) + 'MB, FILEGROWTH = ' + CONVERT (VARCHAR (20),@autoGrowth) + 'MB);' + CHAR(10) + 'GO' WHILE ( @initialFileSize <@newLogSize) BEGIN SET @initialFileSize = @initialFileSize + @increment PRINT 'ALTER DATABASE [' + @databaseName + '] MODIFY FILE (NAME = N''' + @physicalname + ''', SIZE = ' + CONVERT (VARCHAR (20),@initialFileSize) + 'MB);' + CHAR(10) + 'GO' END PRINT CHAR(10) PRINT '--Post Step: Check new VLF count' PRINT 'USE [' + @databaseName + ']' +CHAR(10)+'GO'+ CHAR(10)+'DBCC LogInfo;' PRINT CHAR(10)+CHAR(10)+CHAR(10) PRINT '*****************************************************/' -- need for multiple log file FETCH NEXT FROM filecrsr INTO @physicalName, @totalSize END--filecrsr begin CLOSE filecrsr DEALLOCATE filecrsr FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize END --dbcrsr begin CLOSE dbcrsr DEALLOCATE dbcrsr