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.
- Read the pages from buffer pool, if the pages isn’t there SQL Server will get that data from disk and put in memory.
- Start the lock process, SQL Server will acquire intent-exclusive lock for the table and pages, update row-level locks.
- 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.
- 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.
So, that’s why transaction logs are critical and we have to take care of it.