Every SQL server database has a transaction log that records all transactions and the database modifications made by each transaction. As such the transaction log is an essential component of every database, especially when it comes to disaster recovery.

Each log has a fixed size and with the log recording all transactions, it can become full very quickly. To manage the size the transaction log must be truncated at regular intervals.

To monitor the size of the transaction logs for a database instance, use the command: DBCC SQLPERF (LOGSPACE)

The transaction log supports the following operations:

• Individual transaction recovery.
• Recovery of all incomplete transactions when SQL Server is started.
• Rolling a restored database, file, file group, or page forward to the point of failure.
• Supporting transactional replication.
• Supporting high availability and disaster recovery solutions: Always On Availability Groups, database mirroring, and log shipping

The transaction log for each database is stored in a separate file with the extension ldf. By default the log file will be stored in the Data folder in a typical SQL installation. In the example below replace the pertinent parts of the path with the SQL version you have installed.

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\

The database recovery model affects what is logged.

Simple

No log backups. There is essentially no need to manage the log as space is automatically reclaimed. Operations that require the transaction log backups are not supported by the simple recovery model, including: log shipping, always on, database mirroring, media recovery (without data loss), point in time restores.

You will only be able to recover data up to the end of the last full backup.

Full

Generates full log records.

This is the best choice for data security as in most cases data loss should not occur. You can also restore to any point in time.

Bulk logging

Uses minimal logging for most bulk operations.

You will only be able to recover data up to the end of the last full backup.