SQL Server CHECKPOINT also generates LSNs in Log file

Hi Friends,

In one of my recent training, one participant had an understanding that Log Sequence Numbers (LSNs) are only generated by user transactions / user activity. Well, that’s not true. LSNs will be generated by Checkpoint process also. Whenever Checkpoint is issued manually or automatically by SQL Server Checkpoint begin LSN and CheckPoint end LSN are written to the Log file. Here is a small example:

Create a test database.

CREATE DATABASE TributeDB;
GO

Create a test table.

USE TributeDB;
GO
CREATE TABLE Tributes (tributeID INT, LegendName varchar(50), T_Message varchar (100));
GO

Insert a record.

INSERT INTO Tributes VALUES (1, 'Steve Jobs', 'No words can express your contribution to mankind - RIP Steve JObs.');
GO

Use fn_dblog to observe the LSNs in the log file.

SELECT [Current LSN], [Checkpoint Begin], [Operation] FROM fn_dblog (NULL, NULL);
GO

You will see a bunch of records out there…

Let’s fire a CHECKPOINT manually and run the fn_dblog query again.

CHECKPOINT;
GO
 
SELECT [Current LSN], [Checkpoint Begin], [Operation] FROM fn_dblog (NULL, NULL);
GO

Now, you would see only 2 records and the operation type as LOP_BEGIN_CKPT & LOP_END_CKPT.

   

Current LSN             Checkpoint Begin         Operation

———————– ———————— ——————————-

0000002d:000000a6:00be  2011/10/14 22:28:23:267  LOP_BEGIN_CKPT

0000002d:000000f4:0001  NULL                     LOP_END_CKPT

Every time you fire a CHECKPOINT (or when it is automatically fired by SQL Server), new LSNs will come in (observe the time date/time also). Run again and compare the values.

CHECKPOINT;
GO
 
SELECT [Current LSN], [Checkpoint Begin], [Operation] FROM fn_dblog (NULL, NULL);

Compare the results.

Current LSN             Checkpoint Begin         Operation

———————– ———————— ——————————-

0000002d:000000f5:0001  2011/10/14 23:05:49:827  LOP_BEGIN_CKPT

0000002d:000000f6:0001  NULL                     LOP_END_CKPT

(2 row(s) affected)

Thus, its not only user activity that generates LSNs.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server CHECKPOINT also generates LSNs in Log file”

  1. Amit,

    Can you tell us – why checkpoint is part of database Log?

    There is a an abvious reason behind loggin checkpoint in Database log. But, I want to listen your thoughts.

  2. In short,
    SQL Server uses checkpoints to minimize the recovery time for databases. for example : whenever SQL Service comes up after system crash then SQL Server will check the last known good checkpoint from boot page then it will start recovery : redo (roll forward for commited transactions and ) and undo (roll backward for uncommited transactions) operations from that checkpoint by using transaction log (obiously all these transactions were logged inside the transaction log due to write ahead logging behaviour)

    Thanks:

    Prince Kumar Rastogi

Leave a Reply

Your email address will not be published.