Logging

Posted by

Every database has a transaction log that records all transactions and the database modifications made by each transaction

The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.

How that works? What steps SQL Server do to keep the consistent state?

Taking a example I want to update 10 rows in my table and those rows are using 2 pages, let’s see step by step SQL Server does.

  1. Read the pages from buffer pool, if the pages isn’t there SQL Server will get that data from disk and put in memory.
  2. Start the lock process, SQL Server will acquire intent-exclusive lock for the table and pages, update row-level locks.
  3. After all rows to be updates are locked, it’s time to convert the update lock to exclusive lock and make the change to the page in memory. This process will create a log record describing the changes were made.
  4. Next the transaction is ready to commit. This step will write the log records to the transaction log on disk and will acknowledge the commit to the user.

The changed data still in the buffer pool and written in the transaction log on disk.  The data will stay in memory util a checkpoint runs and write the changes to the data files.

9012805ba7742e521e941ec1ec5838b1--sql-server-logs

So, that’s why transaction logs are critical and we have to take care of it.

How to manage the transaction log

How to read transaction logs

 

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s