mssqltips logo

Why does SQL Server require a transaction log?



By:

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:

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, September 18, 2019 - 12:19:24 PM - Raheem Back To Top

Hi,

How does the rollback work? does it mean that the statement where the failure occured will be deleted?

Thank you



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools