2

My full back-up starts daily at 02:00 AM. It takes about 3 hours.
I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours).
I can verify the LSN sequences of T-log backups when I "restore headeronly".
However, there seem to be a gap between the last LSN of the full backup and the T-log backup taken immediately after backup had started (i.e 02:15)

Below is restore headeronly output from the full backup started at 02:00

FirstLSN: 76583000073086500011
LastLSN: 76595000084143900001

...and this is the restore headeronly output from the log backup started at 02.15

FirstLSN: 76570000086250400001
LastLSN: 76571000009693900001

Shouldn't the last LSN of the full back up and first LSN of log back up be the same?
I compared the LSNs of the log back up taken at 05:15 and 05:00 (when the full back up has finished) but there is no sequence again.
I also checked RESTORE FILELISTONLY of the full backup and CreateLSN, DropLSN of log file is 0. What is it that I might be missing here? Thank you.

Improve this question
2
  • 1
    You must be aware that full backup does not affect Transaction log backup chain in anyway so its pointless finding correlation between LSN
    – Shanky
    Commented Dec 10, 2014 at 18:38
  • I asked a question very similar to this a few months ago. dba.stackexchange.com/questions/72421/…
    – Kris Gruttemeyer
    Commented Dec 10, 2014 at 21:03

2 Answers 2

Reset to default
1

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. (http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)

Improve this answer
1

A transaction log contains transaction details for many parallel both transactions that are (a) completed or (b) still processing.

A transaction log backup is intended to harden the log file with all data needed to restore the database to the point in time that includes the last completed transaction. This also includes all the transactions still in process, even though they have no known state until they either commit or rollback.

Therefore, the FirstLSN of a log backup needs to include the oldest incomplete transaction so as to be able to cover all cases of committed or rolled back transactions.

Note: That also explains why a long running open transaction may make the database log file grow excessively. The log file must continue to maintain all of the LSNs that contain transactions back to the oldest still open transaction.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.