SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help Understand First_LSN and Last_LSN
 New Topic  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  
 New 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.08 seconds. Powered By: Snitz Forums 2000