November 26, 2012 at 1:12 pm
I would like to retain the data for change data capture log tables to 365 days.
Please let me know the calculation for the retention period for 365 days in the below script.
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = ; --for 365 days
and also after applying the script how to know it is been updated the retention period to 356 days.
Thanks for your help in advance..
November 26, 2012 at 1:43 pm
Mvs2k11 (11/26/2012)
I would like to retain the data for change data capture log tables to 365 days.Please let me know the calculation for the retention period for 365 days in the below script.
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = ; --for 365 days
and also after applying the script how to know it is been updated the retention period to 356 days.
Thanks for your help in advance..
FROM BOL:
http://msdn.microsoft.com/en-us/library/bb510748%28v=sql.105%29.aspx
[ @retention ] =retention
Number of minutes that change rows are to be retained in change tables. retention is bigint with a default of NULL, which indicates no change for this parameter. The maximum value is 52494800 (100 years). If specified, the value must be a positive integer.
retention is valid only for cleanup jobs.
So basic math will get us to 525600. That is 60 minutes in an hour, 24 hours per day for 365 days. (60 * 24 * 365)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 1:45 pm
Oh yeah...to check the retention period.
SELECT [retention]
FROM [msdb].[dbo].[cdc_jobs]
WHERE [database_id] = DB_ID()
AND [job_type] = 'cleanup'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply