SQL Server Profiler showing 9003 Exception when CDC is configured

While troubleshooting a customers environment I encountered the following 9003 Exception error message captured in SQL Server Profiler.

“The log scan number (42:358:1) passed to log scan in database ‘<db name>’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.”

This had me concerned as I thought SQL Server Change Data Capture (CDC) was working correctly.  Exploring further, I used SQL Server function  fn_dblog to retrieve the transaction “42:358:1” being reported in SQL Profiler error text.  Running fn_dblog multiple times, each time increasing the “end” I was able to quickly spot the entire transaction wrapped in LOP_BEGIN_XACT and LOP_COMMIT_XACT.

— Retrieve TLOG entries for CDC Log Scan
select [Current LSN], Operation, [Transaction ID], [Savepoint Name]
from ::fn_dblog(’42:358:1′, ’42:358:3′)

Current LSN             Operation                       Transaction ID Savepoint Name
———————– ——————————- ————– ———————————
0000002a:00000166:0001  LOP_BEGIN_XACT                  0000:000004b4  NULL
0000002a:00000166:0002  LOP_MARK_SAVEPOINT              0000:000004b4  tr_sp_cdc_scan
0000002a:00000166:0003  LOP_COMMIT_XACT                 0000:000004b4  NULL

(3 row(s) affected)

Tracking back the “tr_sp_cdc_scan” “Savepoint Name”, I learned the entire transaction consisted of a “dummy update” CDC makes to periodically update cdc.lsn_time_mapping table and can be ignored.

–Chris Skorlinski, Microsoft SQL Server Escalation Services