Scalar function fn_cdc_get_min_lsn() constantly returns '0x00000000000000000000' for valid table names?

Question

I have Change Data Capture (CDC) activated on my MS SQL 2008 database and use the following code to add a new tabel to the data capture:

EXEC sys.sp_cdc_enable_table
@source_schema ='ordering',
@source_name ='Fields',
@role_name = NULL,
@supports_net_changes = 0;

However, whenever I try to select the changes from the tracking tables using the sys.fn_cdc_get_min_lsn(@TableName) function

 SET @Begin_LSN = sys.fn_cdc_get_min_lsn('Fields')

I always get the zero value.

I tried adding the schema name using the following spelling:

 SET @Begin_LSN = sys.fn_cdc_get_min_lsn('ordering.Fields')

but this didn't help.

score 22 · Accepted Answer · answered Apr 30 '13 at 10:56

22

My mystake was to assume that sys.fn_cdc_get_min_lsn() accepts the table name. I was mostly misguided by the examples in MSDN documentation, probably and didn't check the exact meaning of the parameters.

It turns out that the sys.fn_cdc_get_min_lsn() accepts the capture instance name, not table name!

A cursory glance at my current capture instances:

SELECT capture_instance FROM cdc.change_tables

returns the correct parameter name:

ordering_Fields

So, one should use underscore as schema separator, and not the dot notation as it is common in SQL Server.

answered Apr 30 '13 at 10:56

Alexander Galkin

12,086
12
63
115

...though as @kevin points out, just replacing the dot isn't always going to work – piers7 Mar 26 '15 at 03:07
I'm sure you are correct, but even when I copy paste the instance name from `cdc.change_tables` directly into `fn_cdc_get_min_lsn`, I get the dreaded `0x00` – Nick.Mc Jun 18 '18 at 13:05
April 2021, this helped me.. Thanks @alexander-galkin I somehow mis-interpreted the parameter value as well. – Sudarshan Kadam Apr 12 '21 at 17:21
In addition, it's also necessary to check if the SQL server agent is turned on. – dpen82 Jul 01 '21 at 08:55

score 2 · Answer 2 · edited Aug 12 '20 at 13:34

The above answer is correct. Alternatively you can add an additional parameter capture_instance to the cdc enable

EXEC sys.sp_cdc_enable_table
@source_schema ='ordering',
@source_name ='Fields',
@capture_instance = 'dbo_Fields'
@role_name = NULL,
@supports_net_changes = 0;

then use the capture_instance string in the min_lsn function

SET @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Fields')

will return the first LSN, and not 0x00000000000000000000.

This is partiularly useful when trying to solve the error "An insufficient number of arguments were supplied for the procedure or function cdc..." from SQL when calling

cdc_get_net_changes_Fields(@Begin_LSN, sys.fn_cdc_get_max_lsn(), 'all')

Which simply means "LSN out of expected range"

i think you missed a comma after `@capture_instance = 'dbo_Fields'`, and i think it should be `fn_cdc_get_net_changes_Fields` ... but also this doesn't fix the issue for me :( — Peter McIntyre, Mar 03 '23 at 02:55

score 2 · Answer 3 · answered Jun 18 '18 at 13:13

I know this is mostly already explained in this post but I thought I would put together my evenings journey through CDC

This error:

"An insufficient number of arguments were supplied for the procedure or function cdc..."

Is probably caused by your low LSN being 0x00

This in turn might be because you put the wrong instance name in with fn_cdc_get_min_lsn.

Use SELECT * FROM cdc.change_tables to find it

Lastly make sure you use binary(10) to store your LSN. If you use just varbinary or binary, you will again get 0x00. This is clearly payback for me scoffing at all those noobs using varchar and wondering why their strings are truncated to one character.

Sample script:

declare @S binary(10)
declare @E binary(10)

SET @S = sys.fn_cdc_get_min_lsn('dbo_YourTable')
SET @E = sys.fn_cdc_get_max_lsn()

SELECT @S, @E

SELECT * 
FROM [cdc].[fn_cdc_get_net_changes_dbo_issuedToken2]
(
@S,@E,'all'
)

Scalar function fn_cdc_get_min_lsn() constantly returns '0x00000000000000000000' for valid table names?

3 Answers3