Introduction

Every SQL Server database has at least two files associated with it:

- The Database File (.MDF) and
- The Transaction Log file (.LDF)

Now, Having a firm grasp of the Transaction log is important for both - the database developers as well as the administrators; hence the Transaction log file would be the prime focus of this article.


Transaction Log

The transaction log can be defined as a fundamental component of the database management system to which the SQL Server writes all transactions before writing them to the database.
By having every database change recorded in the Log file, we can assure ourselves that the data can be restored even if a malfunction occurs.

When creating the database, the location of the SQL Server transaction log has to be specified.
Right Click your database in the Enterprise Manager, and Move to properties. You’ll find a Tag named ‘Transaction Log’, that’s where you can set the Initial Size and other file properties of the Transaction Log such as the File Growth Rate and the File Size.

File Growth : It's the rate at which the transaction log files grow. It can be specified as megabytes or percentage. The File Growth property tells SQL Server that when the transaction log reaches a specified point it should automatically grow the file by the amount of growth specified in order to accommodate future transactions.

File Size: Here's the option to set the maximum size of the transaction log file. You've got two choices. It can be set to unrestricted file growth or to the specified amount of megaBYTES.

The Information recorded in the transaction log would contain the following:
• the beginning time of each transaction
• the changes made to the data and
• the actual commit or rollback of each transaction

With this information on hand the Microsoft SQL Server can accomplish data integrity to ensure that consistent data is maintained in the database.

Wondering when this log file is used???
Well, The transaction log is used each time the SQL Server is restarted, when the transactions are rolled back, as well as when the database is to be restored.

If your data tends to change constantly, you’ll have to actively monitor the size of the log file, because the transaction log of the database will keep growing each time a change in the database occurs.
Hence the growth must be planned for to handle the growth accordingly else an error message will be thrown on your screen stating that the transaction log is full!

The Microsoft SQL Server guarantees that all the log records before the check point will be written to the disk safely. The check point I’ve mentioned here represents the point at which the startup recovery must start rolling transactions forward.

In case you need to shrink the Log file, It can be done by executing the DBCC SHRINKDATABASE statement against the owning database, Here’s how you do it:

DBCC SHRINKDATABASE (DatabaseName, 10)
GO