quarta-feira, 15 de março de 2017

How to change and query the retention period of CDC cleanup job [SQLSERVER]

We can use a simple way to manage the retention period of CDC cleanup routine.
Be sure to set the database you want to change because the stored procedure use the current database.
I set up 90 days retention, using 90*24*60 minutes as argument, but you should ensure your database growth will fit in you available space. Keep track of you database size for a few days before go on vacation.

use
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'

sp_cdc_change_job @job_type='cleanup', @retention=129600
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'


Source: http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx

 Anothe good tip in the source is this query, wich brings more information about CDC jobs settings:
select * from msdb.dbo.cdc_jobs

There are some more issues about this topic in the source, but for me the above content was enough to make my day. Hence, that´s all for now, folks! 

Good queries!

Nenhum comentário:

Postar um comentário