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.