Log File Expansion Automation
SQL Server Database Log file expansion can be fairly tedious if you need to make the log bigger in many reasonably-sized growth increments. It can be tedious because you may need to write and execute a large number of ALTER DATABASE ... MODIFY FILE ... commands.
Automatic Log File Expansion lets you dream of food, glorious food! – Still Life of Fruit by Giovanni Stanchi
The following code automatically grows a SQL Server Database log file, using the size and growth increments you configure in the script. If you set the @DebugOnly flag to 1, the script will only print the commands required, instead of executing them. This allows you to see what exactly will be executed ahead of time. Alternately, you could copy-and-paste the commands into a query window and execute them one-by-one.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
/* Purpose: Automates expansion of a database Log File By: Max Vernon Source: https://www.sqlserverscience.com/recovery/log-file-expansion/ */ DECLARE @DBName sysname = N'database_name_here'; DECLARE @LogFileMinimumSize int = 5000; --measured in MB DECLARE @LogFileGrowthIncrement int = 500; --measured in MB DECLARE @DebugOnly bit = 1; --set to 0 to actually grow the log DECLARE @MaxLogFileSize int; DECLARE @LogFileName sysname; DECLARE @SizeNow bigint; DECLARE @cmd nvarchar(max); --get the name of the most recently added log file SET @LogFileName = COALESCE(( SELECT TOP(1) mf.name FROM sys.master_files mf WHERE mf.type_desc = N'LOG' AND mf.database_id = DB_ID(@DBName) ORDER BY mf.file_id DESC ), N''); --get the current size of the logfile, in MB SET @SizeNow = COALESCE(( SELECT CONVERT(bigint, mf.size) * 8192 / 1048576 FROM sys.master_files mf WHERE mf.database_id = DB_ID(@DBName) AND mf.name = @logfilename ), @LogFileMinimumSize); --get the maximum size of the logfile, in MB SET @MaxLogFileSize = COALESCE(( SELECT CONVERT(bigint, mf.max_size) * 8192 / 1048576 FROM sys.master_files mf WHERE mf.database_id = DB_ID(@DBName) AND mf.name = @logfilename ), @LogFileMinimumSize); IF @MaxLogFileSize > @LogFileMinimumSize BEGIN --grow the log if less than the minimum size, in increments of @LogFileGrowthIncrement MBs IF @logfilename <> N'' BEGIN WHILE (@SizeNow) < @LogFileMinimumSize BEGIN SET @SizeNow = (@SizeNow / @LogFileGrowthIncrement) * @LogFileGrowthIncrement + @LogFileGrowthIncrement; SET @cmd = N'ALTER DATABASE ' + QUOTENAME(@DBName) + N' MODIFY FILE (NAME=' + QUOTENAME(@LogFileName) + ', SIZE = ' + CONVERT(nvarchar(max), @SizeNow) + N'MB);'; IF @DebugOnly = 0 BEGIN EXEC sys.sp_executesql @cmd; WAITFOR DELAY '00:00:02'; END ELSE BEGIN PRINT @cmd; END END END END ELSE BEGIN DECLARE @msg nvarchar(1000); SET @msg = N'Log File Maximum Size is less than requested @LogFileMinimumSize.'; RAISERROR (@msg, 14, 1); END |
Proactively monitoring and maintaining the SQL Server Transaction Log is one of the most important aspects of database management. The first being always take-and-test your backups! If you don’t have a backup, nothing else matters. However, once you’ve got backups setup, ensuring the transaction log is in tip-top shape is of paramount importance to good performance and up-time.
If you need to shrink-and-grow a log file to reduce the number of Virtual Log Files present in the log, check this post for a script that does that automatically.
Also, check out the rest of our series on recovery and our database tools.
Let me know if you enjoyed this post, and if you found the script useful. Alternately, if you think this needs further clarification, or more detail, please let me know in the comments!
[…] Hannah Vernon shows how you can automatically expand log files to optimize VLF counts: […]