April 6, 2015 at 10:29 am
Hi all,
I want to build a table I can archive off old CDC records to. The key on the change table is __$start_lsn, __$end_lsn, and _$seqval
I guess I'm curious if I can just key my archive table the same, or if, say, after server restarts, LSNs will get recycled and potentially cause a key violation.
April 6, 2015 at 10:58 am
Yep, LSNs are reused. It won't make for a great unique identifier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2015 at 2:49 am
No. LSNs are never reused, they're unique within the database.
If an LSN ever reset to a lower value it would result in the database being suspect and needing a restore. The ever-increasing nature of the LSN is critical to SQL's transaction and recovery processes
This explains how a LSN is constructed
http://sqlmag.com/blog/it-possible-run-out-log-sequence-numbers
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
April 7, 2015 at 4:07 am
GilaMonster (4/7/2015)
No. LSNs are never reused, they're unique within the database.If an LSN ever reset to a lower value it would result in the database being suspect and needing a restore. The ever-increasing nature of the LSN is critical to SQL's transaction and recovery processes
This explains how a LSN is constructed
http://sqlmag.com/blog/it-possible-run-out-log-sequence-numbers
Did I have another major brain f**t? Sorry about that. Not sure where that one came from.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2015 at 4:12 am
I had to double-check to be sure.
The key is that when a VLF is reused, it's sequence number increases so it never has the same sequence number as a previous VLF and the sequence number is the first part of the LSN (VLF Sequence number:Log Block:Offset within block)
If you had a log with 8 VLFs, when it's first created the sequence numbers would be (for example) 1, 2, 3, 4, 5, 6, 7, 8. When the log wraps (after truncation) and the first VLF in the file is reused, that first VLF would then have a sequence number of 9, the next one would get 10 and so on.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply