Solved

SQL 2008 Change Tracking Retention Period failing

Posted on 2010-11-17
9
1,198 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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