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

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help Understand First_LSN and Last_LSN

Author  Topic 

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-04-16 : 09:57:12
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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-16 : 20:21:20
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
   

- Advertisement -