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 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 --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 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 , 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 OPEN dbcrsr FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize, @physicalName 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 '*****************************************************/' FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize,@physicalName END --dbcrsr begin CLOSE dbcrsr DEALLOCATE dbcrsr