• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1217
  • Last Modified:

SQL 2008 Change Tracking Retention Period failing

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
rmm2001
Asked:
rmm2001
  • 5
  • 4
2 Solutions
 
ralmadaCommented:
0
 
rmm2001Author Commented:
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
 
ralmadaCommented:
>>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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
rmm2001Author Commented:
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
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
rmm2001Author Commented:
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
 
ralmadaCommented:
>>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
 
rmm2001Author Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now