CDC fn_cdc_get_all_changes_dbo_ error 313 "An insufficient number of arguments..."

Question

There's no shortage of topics covering this function and error "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes". I've checked most of them, but can't figure out what's wrong.

The problem here specifically is that I can't even reproduce this. It just appears randomly a few times per day or two, and goes away in mere seconds, so usually I can only see it in the job history as a failed run.

All sources I've browsed say it typically comes from using the function to fetch data from a time range where the capture instance doesn't have data. But as the code below will show you, the values are checked for just these type of exceptions before running the function.

DECLARE @END_LSN BINARY(10), @MIN_TABLE_LSN BINARY(10);
SELECT @END_LSN = sys.fn_cdc_get_max_lsn();

SELECT @MIN_TABLE_LSN = MAX(__$start_lsn) FROM MY_AUDIT_TABLE

IF @MIN_TABLE_LSN IS NOT NULL
    SELECT @MIN_TABLE_LSN = sys.fn_cdc_increment_lsn(@MIN_TABLE_LSN)
ELSE
    SELECT @MIN_TABLE_LSN = sys.fn_cdc_get_min_lsn('dbo_MY_AUDIT_TABLE')

IF @MIN_TABLE_LSN IS NOT NULL
BEGIN
    INSERT INTO MY_AUDIT_TABLE (...columns...)
    SELECT  ... columns...
    FROM cdc.fn_cdc_get_all_changes_dbo_MY_SOURCE_TABLE(@MIN_TABLE_LSN, @END_LSN, 'all update old') C
    JOIN cdc.lsn_time_mapping T WITH (NOLOCK) ON T.start_lsn = C.__$start_lsn
    ORDER BY __$start_lsn ASC, __$seqval ASC
END

Now the only remaining alternatives which some people have even suggested, is that this code may sometimes pick the latest change from the AUDIT table and then increment that to an LSN that doesn't yet exist. But I've tested this manually tons of times, and it gives no errors. Also, using an @END_LSN value derived from another CDC table, where this particular MY_AUDIT_TABLE doesn't have records that far yet, works perfectly as well.

The only way I can produce this error manually, is by giving the function a newer @END_LSN value than what exist in lsn_time_mapping table. But such a scenario is only possible if SQL Server can actually create CDC-table records with start_lsn's that don't yet exist in lsn_time_mapping, and I hardly think that's possible. Or is it? That would mean that you can't reliably map an lsn to a datetime at the time the row has just become available.

Thanks for help and explanations again, as usual. :)

Warner

As you've stated, I've only seen this error when the LSN arguments represent a nonsense interval (i.e. min >= max). My advice would be to replace your check with if @min_table_lsn < @end_lsn since that needs to be true for the get changes function to work properly. – Sherwin

Yeah but I don't understand why I can't reproduce the problem. I doubt the function uses some math.Random() function to work perfectly fine with greater min > max value when I test it, but then sometimes returns an error when the job & procedure do the same? But yeah, I think I'll try that and see if I get those errors the next few days anyway. Thanks! :) – Warner

Stranger things have happened. My only advice would be to log the values from within the procedure. I can't tell you what you'll find, but it's almost guaranteed to be interesting. – Sherwin

I am facing exact same issue, weird behavior from CDC at random times, mainly when the job runs with high frequency like continuously executing job. Did you get any solution or root cause? – Improvisator

Answer 1

This

SELECT @MIN_TABLE_LSN = sys.fn_cdc_increment_lsn(@MIN_TABLE_LSN)

just adds 1 to @MIN_TABLE_LSN. It's not necessary that new @Min_Table_Lsn exists. So if you don't have any changes, @END_LSN may be lower than @Min_Table_Lsn and cdc.fn_cdc_get_all_changes_dbo_MY_SOURCE_TABLE() function may fail.

Note that "CDC Scanner job" adds a dummy LSN (with transaction id 0x0) every 5 min to the cdc.lsn_time_mapping table, that's why you may have scenarios when there are no changes and the script may succeed or not, depending on when you run it.

As mentioned above, you should change the condition that Min LSN is lower or equal to End LSN

Triley

Answer 2

i was able to fix this by using the "full name"

Eg. table is dbo.lookup_test you need to pass dbo_lookup_test in sys.fn_cdc_get_min_lsn.

DECLARE @from_lsn binary(10), @to_lsn binary(10);  
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_lookup_test');  
SET @to_lsn   = sys.fn_cdc_get_max_lsn();  
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_lookup_test
  (@from_lsn, @to_lsn, N'all');  
GO

Obviously the code above already took my answer as consideration, but the table naming requirement "{schema}_{tablename}" was not apparent and SQL Server was not helping by giving a misleading error message.

Please also note the lsn should be declared as binary(10). It can't be simply declared as binary. It will throw the same error.

Malvia

Answer 3

Simple - if the BeginLsn/FromLsn that you are trying to supply to get_all_changes function, is not available in the change table, this error is thrown. By default set it to minLsn (get_min_lsn) and pass it to function. With the retention period in effect, after every x number of days, the change table data get's cleaned, so you should make sure you are setting FromLsn to an available value in CT table.

Atalanta

Answer 4

This error can occur due to different issues with the input parameters of fn_cdc_get_all_changes_.... In my case, SELECT sys.fn_cdc_get_max_lsn(); was returning NULL because SQL Server Agent was not running (see this answer). NULL value passed as @to_lsn to fn_cdc_get_all_changes_..., resulted in "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes".

Hen