Microsoft® SQL Server® 2012 Bible

958

Part VII: Monitoring and Auditing

INSERT HumanResources.Department (Name, GroupName)

VALUES ('CDC New Row', 'SQL Rocks'),

('Test Two' , 'CDC Rocks ');

UPDATE HumanResources.Department

SET Name = 'Changed Name'

WHERE Name = 'CDC New Row';

INSERT HumanResources.Department (Name, GroupName)

VALUES ('Row Three', 'PBM Rocks'),

('Row Four' , 'TVP Rocks');

UPDATE HumanResources.Department

SET GroupName = 'T-SQL Rocks'

WHERE Name = 'Test Two';

DELETE FROM HumanResources.Department

WHERE Name = 'Row Four';

With fi ve transactions complete, there should be some activity in the log. The following

DMVs can reveal information about the log:

SELECT *

FROM sys.dm_cdc_log_scan_sessions

SELECT *

FROM sys.dm_repl_traninfo

SELECT *

FROM sys.dm_cdc_errors

Examining the Log Sequence Numbers

The data changes are organized in the change tables by log sequence number (LSN).

Converting a given date time to LSN is essential to working with CDC. The sys.fn_cdc_

map_time_to_lsn function is designed to do just that. The fi rst parameter defi nes the

LSN search (called LSN boundary options), and the second parameter is the point in time.

Possible searches are as follows:

■ (^) Smallest greater than
■ (^) Smallest greater than or equal
■ (^) Largest less than
■ (^) Largest less than or equal
Each of the search options defi nes how the function can locate the nearest LSN in the
change tables.
c41.indd 958c41.indd 958 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM
http://www.it-ebooks.info