Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

Change Data Capture not working

Hello,

In my application i want to track all the changes that the user has made, therefore i ended up using the Change Data Capture in SQLSERVER 2008 Enterprise edition.

I followed the link below and enabled the CDC for the database and one table which i would like to track.

http://www.codeproject.com/Articles/166250/Microsoft-SQL-Server-2008-Change-Data-Capture-CDC

After i enabled this, i checked tracking on the table and it was enabled.

So, i went a step further and made some changes in the table like one delete, 1 insert and 2 updates.

Now, when i try to check the captured data by executing this....

DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN 
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_ClientServices')
-- get the last LSN 
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns net changes
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_ClientServices(
 @Begin_LSN, @End_LSN,'ALL');

Open in new window


I am getting an error.
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ... .

I think the reason for this is the End_LSN being NULL. There is some value in Begin_LSN, but the End_LSN is null and that is reason why it is throwing the error mentioned above.

Can anyone help me with this? Why the End_LSN is returning null?

Appreciate the help
Thanks
0
shahjagat
Asked:
shahjagat
  • 13
  • 12
1 Solution
 
Scott PletcherSenior DBACommented:
Is there a "hard" line feed after the "("?  I've noticed that sometimes seems to "confuse" SQL.

Make sure the parameters immediately follow the open paren:

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_ClientServices (@Begin_LSN, @End_LSN,'ALL');

If you want to use a hard return, try it before the (:

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_ClientServices
    (@Begin_LSN, @End_LSN,'ALL');
0
 
Scott PletcherSenior DBACommented:
Or maybe no changes have actually been captured yet?

That's the only legitimate reason I can think of for a NULL end lsn.
0
 
Scott PletcherSenior DBACommented:
Make sure the SQL Agent is running and the capture job can run properly.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
shahjagatAuthor Commented:
Scott,

 Thanks.. That was SQL Server Agent. I started it and it is working fine now.
0
 
shahjagatAuthor Commented:
Scott,

 I've read somewhere that the data in the log will be cleared after 3 days. I want to increase that limit. Where can i do that? SQL Server Agent? If so, what is the maximum value I can put there?
0
 
Scott PletcherSenior DBACommented:
EXEC msdb.sys.sp_cdc_change_job
    @job_type = 'CLEANUP',
    @retention = <number_of_minutes_to_retain_data>


>> what is the maximum value I can put there? <<

The max value for @retention is: 52494800, which is a little less than 100 years -- that should cover you I think :-) .


If you want, you can put an extreme value in for @retention and then do the cleanup yourself.  If you do that, be SURE you do actually run a cleanup, and that it actually works, or the cdc data can get too large.
0
 
shahjagatAuthor Commented:
It is not allowing me to keep the history more than 4 weeks. Is there any way to get the log before 4 weeks? Is it saved somewhere in SQL SERVER?
0
 
shahjagatAuthor Commented:
Sorry I didn't see your last comment while i was typing mine.

I executed that code and it was successful. Now when i go to the sqlserver agency history , the" Remove Agent History " was not checked and the value in it is 4 weeks. Is it what it is supposed to be?
0
 
Scott PletcherSenior DBACommented:
Old logs should not be relevant for cdc, only since cdc was enabled.

How many weeks of cdc history do you want to keep?

[I was going by Books Online for the max value; I've never actually tried to set it to more than 4 weeks.]
0
 
Scott PletcherSenior DBACommented:
Hmm, I think it's still supposed to be checked.

However, the problem may be that I did not leave out two key steps :-) .

OOPS, sorry about that.

After you change the job params, the job has to be stopped and restarted, like so:

EXEC msdb.sys.sp_cdc_stop_job 'CLEANUP'
EXEC msdb.sys.sp_cdc_start_job 'CLEANUP'


That should do it then.
0
 
shahjagatAuthor Commented:
Limit Size of job history log is checked . i gave " maximum job history log size" and "Maximum job history rows per job " as 999999. Is this correct in my scenario?

I want to all those changes for ATLEAST  1 year. Right now, I am using it in 1 table.Eventually i'll be using this CDC in more tables.
0
 
Scott PletcherSenior DBACommented:
Ooh, I wouldn't use cdc to keep data for that long.  For that degree of retention, I would create a history table (and compress it) and put the historical data in there.

You can use cdc to generate the history table.

However, that's just my preference and technically I think you should be OK using cdc for 1 year.

The job history log can be MUCH shorter than that.  That is just history for the job itself, not for the cdc data.  A couple thousand rows should easily handle all job history needs.



CORRECTION of type:
>> However, the problem may be that I did not leave out two key steps :-) . <<

Should have read:
However, the problem may be that I did leave out two key steps :-) .
0
 
shahjagatAuthor Commented:
How do you create a history table? Is it by writing the whole record in to the history table from the web application ? If so, don't you think it is a overhead?
0
 
shahjagatAuthor Commented:
FYI, I am working on .net 3.5 Web app - C#
0
 
Scott PletcherSenior DBACommented:
Not from the web app.  I would use a T-SQL script, run by a SQL job.  You would have to use the cdc function to get the actual data to store, and confirm it was captured, before purging older cdc data.

But, again, that's not required.  If you want to keep data for a year under cdc, you should be able to.
0
 
shahjagatAuthor Commented:
Thank you Scott.

 I ran the script you have given and like i said, the" remove Agent history "option was NOT checked and the value in it is 4 weeks.
EXEC msdb.sys.sp_cdc_change_job
    @job_type = 'CLEANUP',
    @retention = <number_of_minutes_to_retain_data>

Is that all? Would it keep the history for 1 year?
0
 
Scott PletcherSenior DBACommented:
One last step, as I noted in an earlier post:

After you change any job's params, that job has to be stopped and restarted, like so:

EXEC msdb.sys.sp_cdc_stop_job 'CLEANUP'
EXEC msdb.sys.sp_cdc_start_job 'CLEANUP'

After that, you should be set.  CDC should keep the change data for 1 year.
0
 
shahjagatAuthor Commented:
Forgot to tell you. I did that and for the first one it has thrown me an error for the first one   "Cleanup refused because the job is not running" and for the second one "Started Successfully".
0
 
Scott PletcherSenior DBACommented:
That's OK; you can ignore that first error.  As long as the job is activated now, you should be fine.
0
 
shahjagatAuthor Commented:
Thank you Scott.
0
 
shahjagatAuthor Commented:
Having more records in history would effect the performance of the database?


Can we set index for that history table?
0
 
Scott PletcherSenior DBACommented:
I don't think the size of the history will directly affect the performance of the main table.

But for the database as a whole, yes, somewhat, since it will take up extra space in the database.

I think SQL itself takes care of any indexing on the cdc tables.
0
 
shahjagatAuthor Commented:
I am aware that it would take some extra space. I wouldn't mind unless it effects the performance of the database which wouldn't happen like you said.

So, we don't have to set the index for cdc tables?
0
 
Scott PletcherSenior DBACommented:
No, don't try to index them.

MS recommends that you never even query the cdc tables directly, instead access them using only the functions they provide.
0
 
shahjagatAuthor Commented:
Right now, I am querying like this.
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_ClientServices(
 @Begin_LSN, @End_LSN,'ALL');

Open in new window


Aren't these the functions provided by MS?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now