Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help Understand First_LSN and Last_LSN
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

366 Posts

Posted - 04/16/2004 :  09:57:12  Show Profile  Reply with Quote
I'm trying to determine why occassionaly we receive errors during daily restores indicating earlier log backups exist. Can someone help me understand First_LSN, Last_LSN and Database_Backup_LSN in msdb..backupset? I realize they are pointers to the starting and ending addresses of data contained for a particular backup, but I'm having trouble tracing this information in msdb..backupset.

backup_start_date first_lsn last_lsn database_backup_lsn
-------------------- --------------------------- --------------------------- ---------------------------
Apr 15 2004 4:05PM 3193000001516800001 3193000001643400001 3184000002632600006
Apr 15 2004 7:01PM 3193000001516800001 3193000001684500001 3184000002632600006
Apr 15 2004 11:17PM 3193000001643400001 3215000001783700001 3193000001682900006
Apr 16 2004 6:05AM 3215000001783700001 3215000001991300001 3193000001682900006

I expected to see the First LSN of a T-Log dump to be identical to the last LSN of the previous T-Log dump or DB Backup. In the example above, all entries pertain to T-Log dumps except 7:01PM, which is a database backup.

The 4:05PM T-Log backup has the same First_LSN as the 7:01PM DB Backup. Why?
The 11:17PM T-Log backup has the same First_LSN as the 4:05PM T-Log backup's Last_LSN. I expected it to match the Last_LSN of the 7:01PM DB Backup. Doesn't the DB backup essentially reset the pointers so any T-Log backup that follows should reference it's Last_LSN?
What is database_backup_lsn?

Thanks, Dave

Edited by - DBADave on 04/16/2004 09:58:11


United Kingdom
12543 Posts

Posted - 04/16/2004 :  20:21:20  Show Profile  Visit nr's Homepage  Reply with Quote
A database backup doesn't affect the log backups - otherwise you wouldn't be able to restore a sequence of log backups from a previous backup. One of the benefits of log backupos is that if you have a corrrupt full backup you can go back to a previous backup and still restore the sequence of logs.
So the database backup which include the log has the same first LSN as the previous log - not sure why this is - indicates nothing has been reused maybe.
The 11:17 is following the sequence from the 4:05 and is not affected by the full backup at 7:01.
Think the database_backup_lsn is set by the full backup - could be something to do with completed transactions and checkpoints - I donm't really know but is probably used for sequences in backup sets.

I wrote something involving LSN's a long time ago and probably got info from Inside SQL Server - do you have a copy (you should).

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000