Microsoft® SQL Server® 2012 Bible

964

Part VII: Monitoring and Auditing

The following script uses the sys.fn_cdc_get_min_lsn() function to identify a start-

ing point in the change table and then iterates through the entries sequentially using the

sys.fn_cdc_increment_lsn() function, which fi nds the next entry following the one

passed in as a parameter:

DECLARE

@BeginLSN VARBINARY(10) =

sys.fn_cdc_get_min_lsn('HumanResources_Department');

SELECT @BeginLSN;

SET @BeginLSN = sys.fn_cdc_increment_lsn(@BeginLSN);

SELECT @BeginLSN;

SET @BeginLSN = sys.fn_cdc_increment_lsn(@BeginLSN);

SELECT @BeginLSN;

Result (obviously, your result will be different):

----------------------

0x000000420000136A003D

----------------------

0x000000420000136A003E

----------------------

0x000000420000136A003F

Likewise, CDC can move backward through the entries:

SET @BeginLSN = sys.fn_cdc_decrement_lsn(@BeginLSN);

SELECT @BeginLSN;

Result:

----------------------

0x000000420000136A003E

Removing Change Data Capture

Removing change data capture is a fl exible and simple process, should you decide it isn’t

working or necessary. CDC can be disabled table by table, or for the whole database. When

CDC is disabled for the database, it automatically disables all tables, removing the SQL

Agent jobs, and dropping the custom tracked table functions. There’s no need to remove

CDC from each table individually before disabling CDC from the database:

EXEC sys.sp_cdc_disable_db;

To remove CDC from a specifi c table, use the following system stored procedure:

EXEC sys.sp_cdc_disable_table

@source_schema = 'HumanResources',

c41.indd 964c41.indd 964 7/31/2012 10:17:22 AM7/31/2012 10:17:22 AM

http://www.it-ebooks.info