Log file is growing with Simple Recovery mode

Question

I am trying to learn why the below code is writing to the log file. I am a beginner and have read that, log is not written when the database is in simple recovery mode. But the below code is writing in both FULL and SIMPLE recovery mode. In which cases, the log file gets written with simple recovery mode?

Code:

Declare @val int =1
set nocount on

BEGIN TRAN
while @val <= 100000
begin
    insert into LoadTable values (REPLICATE('P',1000))
    set @val = @val + 1
end
ROLLBACK TRAN

Answer 1

I took all of the information here from Pro SQL Server Internals 2014
https://www.amazon.com/Pro-Server-Internals-Dmitri-Korotkevitch/dp/1430259620

TL;DR;

The recovery mode SIMPLE and FULL differs on how SQL Server will inactivate Virtual Log Files(VLF).
In summary:
1 - "in the SIMPLE recovery model, the active part of transaction log starts with VLF, which contains the oldest of LSN of the oldest active transaction or the last CHECKPOINT";
2 - "in the FULL or BULK-LOGGED recovery models, the active part of transaction log starts with VLF, which contains the oldest of the following:
LSN of the last log backup
LSN of the oldest active transaction
LSN of the process that reads transaction log records"

LSN = Log Sequence Number = unique, auto-incrementing ID

More detailed explanation

Suppose that this is the SQL Server memory model:
1 - Buffer Pool is where SQL Server storage indices, rows etc... in memory;
2 - Log buffer is a little (64KB per database) buffer of the Transaction Log;
3 - Data File is where SQL Server will persist indices, rows etc... in disk;
4 - Transaction log is...well, the Transaction Log in disk.

Suppose that we have a database in the following state.

/--------------- IN MEMORY --------------\/------------ IN DISK -----------\
|--------------------------------------------------------------------------|
|Buffer Pool                |  Log Buffer | Data File     |Transaction Log |
|---------------------------|-------------|---------------|----------------|
|Page 1:24312               |             |Page: 1:24312  |LSN:7213        |
|IsDirty: False             |             |LSN: 4845      |                |
|LSN: 4845                  |             |Page: 1:24313  |                |
|...                        |             |LSN: 2078      |                |
|Page 1:26912               |             |...            |                |
|isDirty:False              |             |Page: 1:26911  |                |
|LSN:1053                   |             |LSN: 2078      |                |
|                           |             |Page: 1:26912  |                |
|                           |             |LSN: 2078      |                |
|---------------------------|-------------|---------------|----------------|

Now suppose a change is made, a simple update.
The first step is to insert the Log Record in the Log Buffer.

/--------------- IN MEMORY --------------\/------------ IN DISK -----------\
|--------------------------------------------------------------------------|
|Buffer Pool                |  Log Buffer | Data File     |Transaction Log |
|---------------------------|-------------|---------------|----------------|
|Page 1:24312               |LSN:7214     |Page: 1:24312  |LSN:7213        |
|IsDirty: False             |Op:Update    |LSN: 4845      |                |
|LSN: 4845                  |Page:1:24312 |Page: 1:24313  |                |
|...                        |OldLsn:4845  |LSN: 2078      |                |
|Page 1:26912               |Row:2        |...            |                |
|isDirty:False              |Tran:T1      |Page: 1:26911  |                |
|LSN:1053                   |PrevLSN:7141 |LSN: 2078      |                |
|                           |             |Page: 1:26912  |                |
|                           |             |LSN: 2078      |                |
|---------------------------|-------------|---------------|----------------|

And then change the data page in memory (I only change the IsDirty to simplify)

/--------------- IN MEMORY --------------\/------------ IN DISK -----------\
|--------------------------------------------------------------------------|
|Buffer Pool                |  Log Buffer | Data File     |Transaction Log |
|---------------------------|-------------|---------------|----------------|
|Page 1:24312               |LSN:7214     |Page: 1:24312  |LSN:7213        |
|IsDirty: TRUE              |Op:Update    |LSN: 4845      |                |
|LSN: 4845                  |Page:1:24312 |Page: 1:24313  |                |
|...                        |OldLsn:4845  |LSN: 2078      |                |
|Page 1:26912               |Row:2        |...            |                |
|isDirty:False              |Tran:T1      |Page: 1:26911  |                |
|LSN:1053                   |PrevLSN:7141 |LSN: 2078      |                |
|                           |             |Page: 1:26912  |                |
|                           |             |LSN: 2078      |                |
|---------------------------|-------------|---------------|----------------|

This goes on until the Log Buffer is full or the transaction is committed. The Commit generates another entry in the Log Buffer where OP is Commit and flushes the whole buffer to the disk.

/--------------- IN MEMORY --------------\/------------ IN DISK -----------\
|--------------------------------------------------------------------------|
|Buffer Pool                |  Log Buffer | Data File     |Transaction Log |
|---------------------------|-------------|---------------|----------------|
|Page 1:24312               |             |Page: 1:24312  |LSN:7213        |
|IsDirty: TRUE              |             |LSN: 4845      |                |
|LSN: 4845                  |             |Page: 1:24313  |LSN:7214        |
|...                        |             |LSN: 2078      |<ALL PROPERTIES>|
|Page 1:26912               |             |...            |                |
|isDirty:False              |             |Page: 1:26911  |LSN:7215        |
|LSN:1053                   |             |LSN: 2078      |Op:Commit       |
|                           |             |Page: 1:26912  |                |
|                           |             |LSN: 2078      |LSN:7216        |
|                           |             |               |Op:Checkpoint   |
|---------------------------|-------------|---------------|----------------|

At this point SQL Server will answer the client that the transaction succeed. It is worth pointing that the Dirty Page in memory has not been sent to disk yet. At this points if something happened SQL Server would be capable of recovering all changes to this exact point.
This technique is called Write Ahead Logging and for more information see:

Repeating History Beyond ARIES
http://www.vldb.org/conf/1999/P1.pdf

In some moment a Checkpoint process will create a CHECKPOINT operation that flushes all Dirty pages from the Buffer Pool to the disk. Checkpoint operations also appears in the Transaction Log as the example above shows.

With this in mind we can see how SQL Server treats the Transaction Log.

Virtual Log Files

The Transaction Log on the disk are sub-divided in Virtual Log Files (VLF). You can see this running:

DBCC LOGINFO

The important part is that the Virtual Log Files (VLF) can be categorized as Active or Inactive.

SQL Server only use the active parts of the Transaction Log in its recovery model. So the difference between SIMPLE and FULL is when a VLF becames Inactive. SQL Server inactivate a VLF because the Transaction Log is a wraparound file, wich means, "when the end of the logical log file reaches the end of physical file, the log wraps around it". For example:

/------ACTIVE-----\/----------------INACTIVE----------------\/--------ACTIVE---\
|------------------------------------------------------------------------------|
|        |         |          |         |         |         |        |         |
| VLF1   |   VLF2  |    VLF3  |  VLF4   |   VLF5  |   VLF6  |  VLF7  |  VLF8   |
|        |         |          |         |         |         |        |         |
|------------------------------------------------------------------------------|

So if for some reason no VLF become inactive the Transaction Log will need to grow infinitely.

IN SIMPLE RECOVERY

Going back to the example. After the checkpoint, and everything is flushed to the disk, SQL Server in SIMPLE recovery will maintain activated only the VLF that:
1 - contains the oldest of the LSN of the oldest active transaction; or
2 - the last checkpoint.

For example:

Before a Checkpoint

/------INACTIVE---\/----------------ACTIVE-------\/---------INACTIVE-----------\
|------------------------------------------------------------------------------|
|        |         |          |         |         |         |        |         |
| VLF1   |   VLF2  |    VLF3  |  VLF4   |   VLF5  |   VLF6  |  VLF7  |  VLF8   |
|        |         |          |         |         |         |        |         |
|------------------------------------------------------------------------------|
                   ^     ^        ^          ^    ^
                   |     |        |          |    |> End of logical LOG file
                   |     |        |          |> Current LSN 
                   |     |        |> Minumin LSN (Oldest Active Transaction)
                   |     |> Last Checkpoint
                   |> Start of Logical LOG file

After the Checkpoint

/------INACTIVE---------------\/----ACTIVE-------\/---------INACTIVE-----------\
|------------------------------------------------------------------------------|
|        |         |          |         |         |         |        |         |
| VLF1   |   VLF2  |    VLF3  |  VLF4   |   VLF5  |   VLF6  |  VLF7  |  VLF8   |
|        |         |          |         |         |         |        |         |
|------------------------------------------------------------------------------|
                              ^   ^          ^    ^
                              |   |          |    |> End of logical LOG file
                              |   |          |> Current LSN (Checkpoint Occurs)
                              |   |> Minumin LSN (Oldest Active Transaction)
                              |> Start of Logical LOG file

SQL Server have inactivated the VLF3 that contained the last checkpoint because:
1 - The new Checkpoint forced all Dirty pages in memory to the disk. So there is no need to redo any changes that were stored in the VLF3 because the oldest active transaction is in VLF4;
2 - But, because of this we still need VLF4 to support rollback of all active transactions.

IN FULL RECOVERY

The same process happens in FULL recovery, but now the last VLF that will stay active will be the oldest from:
1 - LSN of the LAST LOG BACKUP;
2 - LSN of the OLDEST ACTIVE TRANSACTION; or
3 - LSN of the process that reads transaction log records.

For example

/------INACTIVE---------------\/----ACTIVE-------\/---------INACTIVE-----------\
|------------------------------------------------------------------------------|
|        |         |          |         |         |         |        |         |
| VLF1   |   VLF2  |    VLF3  |  VLF4   |   VLF5  |   VLF6  |  VLF7  |  VLF8   |
|        |         |          |         |         |         |        |         |
|------------------------------------------------------------------------------|
                              ^   ^       ^  ^    ^
                              |   |       |  |    |> End of logical LOG file
                              |   |       |  |> Current LSN (Checkpoint Occurs)
                              |   |       |> Minumin LSN (Oldest Active Transaction)
                              |   |> Replication log Reader
                              |> Start of Logical LOG file

in this example the Replication log Reader is forcing VLF4 to stay active.

or

/------INACTIVE---\/----------------ACTIVE-------\/---------INACTIVE-----------\
|------------------------------------------------------------------------------|
|        |         |          |         |         |         |        |         |
| VLF1   |   VLF2  |    VLF3  |  VLF4   |   VLF5  |   VLF6  |  VLF7  |  VLF8   |
|        |         |          |         |         |         |        |         |
|------------------------------------------------------------------------------|
                   ^   ^           ^       ^  ^    ^
                   |   |           |       |  |    |> End of logical LOG file
                   |   |           |       |  |> Current LSN (Checkpoint Occurs)
                   |   |           |       |> Minumin LSN (Oldest Active Transaction)
                   |   |           |> Replication log Reader
                   |   |> Last Transaction Log Backup      
                   |> Start of logical LOG file

and in this example the "last transaction log backup" is forcing the VLF3 to stay active.

I hope these helps to understand a little better how SQL Server works.

Answer 2

First of all your understanding that nothing is written to log file when the database is in simple recovery mode is WRONG.

SQL Server writes to the Log file in all recovery modes, the only difference is In simple recovery mode it automatically reclaims the log space (when it can) and also logs minimum stuff to maintain transaction (just incase if you have to rollback one).

Whereas in full recovery mode we have to take Transaction log backups to make the space available for SQL Server to reuse for further logging.

Now going back to your example:

Declare @val int =1
set nocount on

BEGIN TRAN                --<-- Your Transaction starts here 
while @val <= 100000
begin
    insert into LoadTable values (REPLICATE('P',1000))
    set @val = @val + 1
end
ROLLBACK TRAN             --<-- Your Transaction ends here 

In your example, after the transaction has begun and before it ends (rollback/commit) there is a lot of activity going on, SQL Server needs to log this activity just in-case if you decide to rollback the transaction just like you did, hence the more and more logs will be written to the log file until the transaction is completed (Committed or Rollback).

In this specific example sql server has to keep log of 100000 insert statements just in-case something goes wrong .

---

Another slightly different version of your query could be...

Declare @val int =1
set nocount on


while @val <= 100000
begin

    BEGIN TRAN        --<-- Your Transaction starts here

      insert into LoadTable values (REPLICATE('P',1000))

    ROLLBACK TRAN     --<-- Your Transaction Ends here         

    CHECKPOINT;

        set @val = @val + 1
end

Now in this slightly different version of the same t-sql command there is a lot less activity going on after the transaction has begun and before its comlpleted, hence sql server has to log very little data and transaction file will grow very little if any.

In this example sql server has to keep log of only 1 insert statement at a time because it is committed or rolled back after that point.

Answer 3

Check out further detail on recovery modes here.

DML queries will always write to the log in order to be able to rollback. In basic terms Simple recovery will not keep the logs once the transaction has been committed but they still write when executing.