Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate automagically at some time point? Or do I have to truncate/shrink it manually?

It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.

I'd suggest getting a handle on what typical and maximum log usage is for your system. The query below (not mine, boosted from Glen Berrys DMV scripts) could be run manually or you could capture the output to a table via an agent job. If you log it to a table for a week or so you'll get a picture of typical usage and more importantly, when a process is causing the log to grow beyond what you expect.

SELECT 
     db.[name] AS [Database Name]
   , db.recovery_model_desc AS [Recovery Model]
   , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
   , ls.cntr_value AS [Log Size (KB)]
   , lu.cntr_value AS [Log Used (KB)]
   , CAST(
        CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) 
        AS DECIMAL(18,2)
     ) * 100 AS [Log Used %]
   , db.[compatibility_level] AS [DB Compatibility Level]
   , db.page_verify_option_desc AS [Page Verify Option]
   , db.is_auto_create_stats_on, db.is_auto_update_stats_on
   , db.is_auto_update_stats_async_on, db.is_parameterization_forced
   , db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on
FROM sys.databases AS db
   INNER JOIN sys.dm_os_performance_counters AS lu 
     ON db.name = lu.instance_name
   INNER JOIN sys.dm_os_performance_counters AS ls 
     ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
   AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
   AND ls.cntr_value > 0 
OPTION (RECOMPILE);

Transaction Log Truncation describes both the when and why log truncation occurs.


If log records were never deleted from the transaction log, it would
eventually fill all the disk space that is available to the physical
log files. Log truncation automatically frees space in the logical log
for reuse by the transaction log.

Factors That Can Delay Log Truncation is a useful reference for understanding why your log may fail to truncate and therefore grow larger than expected.

StackExchange Database Administrators Q#4154, answer score: 19