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 a test table.

Insert a record.

Use fn_dblog to observe the LSNs in the log file.

You will see a bunch of records out there…

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

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.

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.