[CDC] Error 22859, 622, 2601

No comments

2015-05-12

When you enable SQL CDC (Change Data Capture) feature on DB and table. There are two job created in SQL Agent
  • cdc.dbnamexxx_capture
  • cdc.dbnamexxx_cleanup
The best practice is to create another SQL “FILE GROUP” and “FILE” then assign CDC tables to the new “FILE GROUP”. I’ve done it. But the job failed and showed the message below

  1: Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. 
  2: Refer to previous errors in the current session to identify the cause and correct 
  3: any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.


and

  1: The Log-Scan Process failed to construct a replicated command from 
  2: log sequence number (LSN) {000015a9:0000395a:001b}. 
  3: Back up the publication database and contact Customer Support Services. 
  4: [SQLSTATE 42000] (Error 18805)  Log Scan process failed in processing log records. 
  5: Refer to previous errors in the current session to identify the cause and correct 
  6: any associated problems. [SQLSTATE 42000] (Error 22859)  
  7: The statement has been terminated. [SQLSTATE 42000] (Error 3621)  
  8: The statement has been terminated. [SQLSTATE 01000] (Error 3621)  
  9: The call to sp_MScdc_capture_job by the Capture Job for database 'xxxxxx' failed. 
 10: Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)

There are two system DMV you can query to see the errors rather than see the job logs.
  • sys.dm_cdc_log_scan_sessions
  • sys.dm_cdc_errors

The easiest way to get SQL version

No comments

2015-05-08

Most of people suggest to use @@VERSION” and “SERVERPROPERTY

But when we manage SQL server, DBA or developer need to do a lots tedious/repeatable tasks to determine the SQL version in order to meet different needs. So the method above is not what I need.

The most convenient way and the easiest way to remember the syntax is

It shows not only the detail SQL version but also the Windows version

But you have to do lots “STRING” function if you want to manipulate the info for program purpose