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