April 10, 2012 at 3:46 am
HI Experts,
Am new to CDC has some doubt on this.I have a table and enabled CDC on it.For a single transaction i may get more than 400 records in one shot.for every single transaction i believe a new LSN will be created for each transaction.Am suing the below code for retrieving all the transaction related to that LSn by below code,
SET @FromLsn = TableName.sys.fn_cdc_increment_lsn(@ToLsn)
SET @ToLsn = TableName.sys.fn_cdc_map_time_to_lsn('largest less than or equal',
GETDATE())
Now what i need is to get only 100 records of that particular transaction LSN.How could i do it?
Thanks in Advance.
April 10, 2012 at 4:01 am
Can you post your sqlserver instance version information ( @@version ) so we are sure we're posting for the correct one ? ( rtm sp1/sp2 R2 R2sp1 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
April 10, 2012 at 4:08 am
HI, It is 2008 R2 SP1.
April 10, 2012 at 4:23 am
sai198503 (4/10/2012)
For a single transaction i may get more than 400 records in one shot.for every single transaction i believe a new LSN will be created for each transaction.
No, LSNs aren't per-transaction, they're per change in the DB. So a transaction that inserts 400 rows will have at least 402 LSNs (the begin, the commit and the 400 inserts), it'll probably have quite a bit more.
Have you tried using TOP in the query?
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 10, 2012 at 6:53 am
HI Gila, I tried inserting 3 records as a single transaction and only 1 new row was inserting into [cdc].[lsn_time_mapping].
April 10, 2012 at 6:57 am
Then that's something specific to CDC, there will be at least 5 LSNs for those 3 rows inserted.
I don't have a DB with CDC enabled to test on, I could create one, can you just explain more what exactly you want to do, what the change patterns are for the table, etc.
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply