Solved

SQL 2008 Change Tracking Retention Period failing

Posted on 2010-11-17
9
1,202 Views
Last Modified: 2012-05-10
HI guys -

I have a database set up with change tracking. Initially I set it up with 3 days as the initial repletion period. Then I changed it (via the interface in ssms) to 30 days. Clicked ok. I checked in the cdc table ( I forget which one) to make sure that it was actually set to 30 and not to 3 and it said 30.

Problem is - it's still dropping my changes after 3 days. So I know I did something wrong.

Any clues what I did/how I can get my data to stay around longer without rebuilding the entire thing?

Thanks!
0
Comment
Question by:rmm2001
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 34157646
0
 
LVL 7

Author Comment

by:rmm2001
ID: 34157882
Sadly no...that just confirms that my retention period is set to 30. And my retention period units are set to 3. Do my retention period units need to be set to 1?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34157943
>>And my retention period units are set to 3<< I'm not following you here. I think retention period unit should be "Days" not a number...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 7

Author Comment

by:rmm2001
ID: 34157963
Here's what I get when I do a select * from sys.change_tracking_databases  .. It's database_id, is_auto_cleanup_on, retention_period, retention_period_units, retention_period_units_desc  (kinda jumbled in the thing below)
database_id is_auto_cleanup_on retention_period retention_period_units retention_period_units_desc
----------- ------------------ ---------------- ---------------------- ------------------------------------------------------------
7           1                  30               3                      DAYS

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 34158090
Oh, I see it now.


Regarding the links I've posted above, so basically you're saying that you have already installed the hotfixes suggested in the links above aren't you?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34158260
Also are you running on a replicated database? Check this link for some pointers
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/320c546e-e454-4da6-bd3b-aa5a3b5d9ebb
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 0 total points
ID: 34158697
1st post: I don't know if I have or not...I generally don't manage the hotfixing. I'll figure out how to check to see if they're there. I'm assuming I have to restart the service in order for them to get applied?

And no on the replication. Just a basic server - couple of databases on it. 2/3 change tracking enabled
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 34158748
>>1st post: I don't know if I have or not...I generally don't manage the hotfixing. I'll figure out how to check to see if they're there. I'm assuming I have to restart the service in order for them to get applied?<<
Yeah, you should make sure you have the latest patch applied. That will probably require you to restart the service, so just be careful with the timing.

0
 
LVL 7

Author Closing Comment

by:rmm2001
ID: 34216196
It was a good shot in the dark but didn't seem to work. I just took off the "auto cleanup" and will do that manually and hope I don't lose my stuff that way. Thanks for the help!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 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