Modify sql transaction log file size - increase or decrease

04 February,2013 by Jack Vamvas

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

Step 1 : Create a database . This syntaxt will use the model database settings

 

--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

 

Step 2: Increase the sql transaction log file physical size

 

--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

 

Step 3 : 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

Tip: Set up an alert for Database has more than 1000 virtual log files which is excessive – informational message 9017

 

-- 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

 Read More on managing SQL Transaction Log files

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Transaction Log files sequential - SQL Server DBA

SQL Server Transaction Log files - performance myth

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Modify sql transaction log file size - increase or decrease


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer