Why the SQL Server Transaction Log Grows Even in Simple Recovery Model (Disk Filling Up)

Document ID : KB000034299
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Why does my SLM SQL Server log fill the disk space even in "Simple" recovery model?

Environment:  

SQL Server back-end Unified Infrastructure Management (UIM) database

Answer: 

SQL Server always uses the log for data modifications, regardless of recovery model used. The recovery model just affects what happens after the transaction completes, so the log's size can grow in either model.

From the following MSDN article:
http://msdn.microsoft.com/en-us/library/ms179355.aspx

"Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint."

One of the things you may want to consider in this scenario is either increasing the disk space or shrinking the file using either "DBCC shrinkfile" or the SQL Enterprise Studio Admin GUI.