Solved

Change Data Capture not working

Posted on 2012-03-15
25
1,113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
25 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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:
Scott Pletcher earned 250 total points
ID: 37726957
Make sure the SQL Agent is running and the capture job can run properly.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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
 

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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 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