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

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