How to shrink SQL Server database transaction log file initial size

Question: I received a database monitoring alert for a disk space full on a server hosting SQL Server. On closer inspection , the drive was full because of an excessively sized transaction log which had consumed the drive space.

This also had a knock-on effect of not allowing any of the other database transaction log files to grow, causing jobs to fail.

I'd like direction on a sqlserver shrink log file

How can I shrink down the physical size of the sqlserver database log file? The database is in Full Recovery mode, with only a FULL BACKUP issued every day.

Answer: Shrinking down the physical log file size is straight forward, but I think it’s important to understand the reasons it occurred causing this outage.

1. Are there any underlying jobs requiring a large amount of drive space? It may be necessary to SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size
2. Why are there no regular log backups? This is the recommended approach to maintain the log file size at a reasonable level.
3. As the database is in FULL RECOVERY mode, will there be a need for a recovery in point-in-time? This will influence how often the database has a log backup applied.

If there is unused space in the log file , you can issue a DBCC SHRINKFILE. In the example , we’re shrinking the size to 200 MB

USE mydb;
GO
DBCC SHRINKFILE('mydb_LOG', 200);

If there is not enough unused space, you’ll need to do a LOG BACKUP to clear down the space and then proceed with the DBCC SHRINKFILE. If the database is already in SIMPLE RECOVERY mode , you cannot issue a LOG BACKUP, this will require a separate process , read more on Is a LOG BACKUP allowed on a SIMPLE RECOVERY database ...

Managing SQL Server transaction log files is an indepth topic that may require a level of analysis. There are substantial efficiencies to be gained from applying some thought to the initial set up and configuration.For some guidelines read SQL Server Install Checklist

SQL LOG FILE AUTOGROW performance ... - SQL Server DBA