Solved

Change Data Capture not working

Posted on 2012-03-15
25
1,009 Views
Last Modified: 2012-03-17
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
Comment
Question by:shahjagat
  • 13
  • 12
25 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37726939
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37726946
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 37726957
Make sure the SQL Agent is running and the capture job can run properly.
0
 

Author Comment

by:shahjagat
ID: 37727004
Scott,

 Thanks.. That was SQL Server Agent. I started it and it is working fine now.
0
 

Author Comment

by:shahjagat
ID: 37727017
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37727048
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
 

Author Comment

by:shahjagat
ID: 37727062
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
 

Author Comment

by:shahjagat
ID: 37727075
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37727077
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37727089
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
 

Author Comment

by:shahjagat
ID: 37727091
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37727105
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:shahjagat
ID: 37727114
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
 

Author Comment

by:shahjagat
ID: 37727127
FYI, I am working on .net 3.5 Web app - C#
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37729353
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
 

Author Comment

by:shahjagat
ID: 37729406
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37729470
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
 

Author Comment

by:shahjagat
ID: 37729671
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37729761
That's OK; you can ignore that first error.  As long as the job is activated now, you should be fine.
0
 

Author Comment

by:shahjagat
ID: 37729793
Thank you Scott.
0
 

Author Comment

by:shahjagat
ID: 37730948
Having more records in history would effect the performance of the database?


Can we set index for that history table?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37730982
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
 

Author Comment

by:shahjagat
ID: 37731398
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37731416
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
 

Author Comment

by:shahjagat
ID: 37732824
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now