If you do not understand the different types of database recovery models then you should review this information.

Recovery Models:

As referenced in this link, https://msdn.microsoft.com/en-us/library/ms189275(v=sql.110).aspx , SQL server has three Database recovery models: Simple, Full and Bulk-logged. We will only cover the first two here as Bulk-logged is not recommended.

• Simple mode: Minimal logs/transactions are written to the log file. This log is truncated after each DB-checkpoint. See below for more information on checkpoints.
• Full mode: All transactions/logs are kept in log files until the log file (must be a transaction log backup) is backed up. Once the log is backed up it can be truncated by SQL Server. If you do not do a transaction log backup the logs will continue to grow indefinitely and they will eventually run the server out of disk space.

Unless you fully understand the use of the Full mode and how to replay logs we recommend using Simple mode.

Transaction Log Truncation:

https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx

Log truncation does not affect the size of the SQL Log file which is stored on the disk. Truncation is performed on “Virtual logs” inside the “Physical” log file. Once a ‘physical’ log file has grown to a certain size, it will never shrink due to log truncation alone. A separate maintenance item, Shrink File, needs to be run against the file in order to reclaim disk space as the Windows operating system sees it.

In the examples below, let us assume each virtual log file is 1MB. The total log file is 5MB. After truncation runs it is still 5MB on disk but has 3MB of ‘virtual’ free space inside the ‘physical’ file. The file will not shrink from a truncation, it only creates available “white space” within the log file which can then be used by SQL for new logs.

From Microsoft:

The following illustrations show a transaction log before and after truncation. The first illustration shows a transaction log that has never been truncated. Currently, four virtual log files are in use by the logical log. The logical log starts at the front of the first virtual log file and ends at virtual log 4. The MinLSN record is in virtual log 3. Virtual log 1 and Virtual log 2 contain only inactive log records. These records can be truncated. Virtual log 5 is still unused and is not part of the current logical log.

The second illustration shows how the log appears after being truncated. Virtual log 1 and Virtual log 2 have been freed for reuse. The logical log now starts at the beginning of Virtual log 3. Virtual log 5 is still unused and it is not part of the current logical log.

Checkpoints

https://technet.microsoft.com/en-us/library/ms189573(v=sql.110).aspx

The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal. A backup is considered an “Internal” checkpoint, along with these others:

• Database files have been added or removed by using ALTER DATABASE
• A database backup is taken
• A database snapshot is created, whether explicitly or internally for DBCC CHECK
• An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.
• An instance of SQL Server is stopped by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.
• Bringing a SQL Server failover cluster instance (FCI) offline

Any of these tasks would trigger a log truncation for a DB in Simple Recovery Mode.

_____________________________________________________________________________________