Modify sql transaction log file size – increase or decrease
Modifying the sql transaction log file size is required on certain occasions. For example, a large INSERT job requiring to preallocate the transaction log file size for performance gains.Read How to find sql transactions with large logs for techniques on finding queries generating large transaction logs
It is not uncommon for the largest time portion of a large transaction to be the sql transaction log file size growth. If the DBA can predict the sql transaction log size , then adjusting the size before the INSERT can save significant time. Read more on SQL Server – Preallocate SQL Transaction Logs for large queries – Initial Size
The ideal time to modify the transaction log file size is when there are no active transactions. Both increase log file size and decrease log file size are managed through the ALTER DATABASE .. MODIFY FILE syntax.
There are some added preliminary steps for the decrease size.
Let’s walk through an example of creating a database based on default settings , increasing the transaction log file size and then decreasing the transaction log file size
Create a database . This syntaxt will use the model database settings
|
1 2 3 4 5 6 7 |
--based on defaault settings in model --note:db is in SIMPLE RECOVERY create database sizetest --check the current size use sizetest GO select * from sys.sysfiles |
Increase the sql transaction log file physical size
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--step 1 : find out the transaction log filename USE sizetest GO select name from sys.sysfiles where fileid = 2 GO USE master GO --increase the file size to 1000MB ALTER DATABASE sizetest MODIFY FILE (NAME = sizetest_log, SIZE = 1000MB); GO |
Decrease the sql transaction log file physical size
if you attempt a ALTER DATABASE with MODIFY FILE you’ll get this error
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.
To decrease the transaction log file size log , truncation must occur to free disk space. To reduce the log file physical size shrink to eliminate incative virtual log files
|
1 2 3 4 5 6 7 8 9 10 |
-- check there are no active transactions USE sizetest GO dbcc shrinkfile(sizetest_log,TRUNCATEONLY) GO ALTER DATABASE sizetest MODIFY FILE (NAME = sizetest_log, SIZE = 1MB); GO |
Alter DB modify log file for all DBs
Is it possible to create a script which changes log file size to let’s say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.
|
1 2 3 4 5 6 7 8 9 |
SET NOCOUNT ON; SELECT 'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] ' + 'MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = 10000 KB)' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) FROM sys.master_files mf JOIN sys.databases d ON mf.database_id = d.database_id WHERE d.database_id > 1 AND d.state_desc <> 'offline' AND mf.type_desc = 'LOG'; |