Solved

Change Data Capture not working

Posted on 2012-03-15
25
1,023 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

13 Experts available now in Live!

Get 1:1 Help Now