By: Brady Upton
SQL Server needs a transaction log? No, SQL Server HAS to have a transaction log to work? Without a log file, your database will not work….at all.
In the next section we’ll talk more about what a transaction is, but this section will cover the transaction log.
The transaction log supports the following:
ROLLBACK TRANSACTION - If a user or application issues the ROLLBACK statement, or if the DB engine detects a failure, the log records are used to roll back the transaction. We will discuss BEGIN, COMMIT, and ROLLBACK TRANSACTION later in this tutorial.
Recover Incomplete Transactions - If you have ever started SQL Server from a failure you may have noticed databases in the (In Recovery) mode:
This is an indication that SQL Server is rolling back transactions that did not complete before the SQL Server was restarted or it is rolling forward all modifications that were recorded in the log but not written to the data file. You may also see this if you have started a restore WITH RECOVERY.
Rolling a restored DB, file, filegroup, or page forward to the point of failure – If SQL Server were to fail and you need to restore the database back to the point in which the failure occurred you can as long as you are using the FULL recovery model. Start with a full backup, then apply the latest differential, and the subsequent transaction log backups up to the point of failure. We will go into more detail later in this tutorial. You can find more about this here.
High availability solutions - Transactional replication, mirroring, and log shipping all use the transaction log. We will discuss how the log is used later in this tutorial.
Last Update: 3/25/2014
