How can we see MinLSN?

Question

How can we see MinLSN?

Shcherbunov Neil

SSCommitted

Points: 1525
More actions
April 1, 2012 at 7:01 am

#254063

Hello!
As you know the MinLSN, according to BOL, is:
...is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN). The MinLSN is the minimum of the:
* LSN of the start of the checkpoint.
* LSN of the start of the oldest active transaction.
...
I want to see - how MinLSN change in my DB while I work with it. I always think, that DBCC LOG(<DB_name>, 3) and column Minimum LSN in result set is the answer, but...
USE master
go
CREATE DATABASE TLogTruncationDemo
ON PRIMARY (
NAME = 'TLogTruncationDemo_Data',
FILENAME = 'C:\TLogTruncationDemo\TLogTruncationDemo_Data.mdf',
SIZE = 10 MB,
MAXSIZE = 100 MB )
LOG ON (
NAME = 'TLogTruncationDemo_Log',
FILENAME = 'C:\TLogTruncationDemo\TLogTruncationDemo_Log.ldf',
SIZE = 2 MB,
/* Set the MAXSIZE = SIZE so that the T-Log does not grow. */
MAXSIZE = 2 MB )
GO
ALTER DATABASE TLogTruncationDemo SET RECOVERY FULL
GO
BACKUP DATABASE TLogTruncationDemo
TO DISK = 'C:\TLogTruncationDemo\TLogTruncationDemo_Backup_01.bak'
GO
USE TLogTruncationDemo
GO
CREATE TABLE TblTLogDEMO (
C1 INT IDENTITY(1, 1) NOT NULL,
C2 INT NOT NULL,
C3 VARCHAR(10) NOT NULL )
GO
BEGIN TRAN
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 3000
CHECKPOINT
GO
DBCC LOG(TLogTruncationDemo, 3)
GO
COMMIT TRAN
--clean up
USE master
go
DROP DATABASE TLogTruncationDemo
Obvious - the "minimum of" in this code is LSN about transaction start, NOT the LSN about checkpoint start. Nevertheless Minimum LSN in result set contain all NULL besides 2 real LSN:
00000047:00000039:0040
00000048:00000088:0182
The first is LOP_BEGIN_CKPT (checkpoint start) that have place at the moment of BACKUP DATABASE command.
The second is LOP_BEGIN_CKPT (checkpoint start, again) that have place at the moment of CHECKPOINT command.
That's all!! So - who is wrong? I am, or Minimum LSN column, or BOL?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Shcherbunov Neil (4/1/2012)
That's all!! So - who is wrong? I am, or Minimum LSN column, or BOL?

The minimum LSN column (which only appears for a end checkpoint) is the minimum LSN of that checkpoint operation, not the minimum of checkpoint and open trans. It's the earliest point where recovery might have to roll transactions forward from.

The oldest open transaction can be found from the checkpoint payload, the row with the operation LOP_XACT_CKPT. That contains the list of transaction IDs of all transactions open when the checkpoint ran.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

p.s. Don't mix up the minimum LSN for database recovery and the minimum LSN for log truncation, because they are not the same thing.

p.p.s The undocumented commands and features tend to use different terms from what the documented ones and documentation do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shcherbunov Neil SSCommitted Points: 1525 More actions · Answer 3

GilaMonster (4/1/2012)
The minimum LSN column (which only appears for a end checkpoint) is the minimum LSN of that checkpoint operation

Yes, you are absolutely right, as it turns out, Minimum LSN column only "check" the MinLSN from checkpoint. It doesn't bother about MinLSN change it's place as result of new transaction (and, I am sure, replication ignored as well).

GilaMonster (4/1/2012)
It's the earliest point where recovery might have to roll transactions forward from.

Exactly. In other words "MinLSN from BOL" != "Minimum LSN column".

GilaMonster (4/1/2012)
Don't mix up the minimum LSN for database recovery and the minimum LSN for log truncation, because they are not the same thing.

Yes, I am aware about distinct. But in any case - thanks so much for you explanations and notes! The image much cleaner for me now.

saurabh.x.sinha Ten Centuries Points: 1125 More actions · Answer 4

So how does sql server identify Minimum LSN for database recovery ie LSN of the start of the oldest active transaction. What process does sql server follow. Can we find same by some query ???