question

Troy_2012 avatar image
Troy_2012 asked

This job doing what - sys.sp_MScdc_cleanup_job ?

Hi... everyone now when we enable the CDC in database and tables we will have 2 jobs , one of them **sys.sp_MScdc_cleanup_job** When I open the comand for this job I found : - **sys.sp_MScdc_cleanup_job** and the schedule:- **Occurs every day at 2:00:00 AM. Schedule will be used starting on 11/1/2013.** When I run this job manual it's succeeded , but I checked all the tables and no data are deleted, I checked online and I just found the definition for this job :- to clean up database changes tables...what is that mean I checked the tables that created when we enable the CDC and they have the same data , so what the job clean what the data in another database : [cdc].[change_tables]. [cdc].[dbo_customer_CT]. [cdc].[ddl_history]. [cdc].[index_columns] [cdc].[lsn_time_mapping]
sql-server-2012change-data-capture
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Troy_2012 avatar image
Troy_2012 answered
The result is 13 rows for the query: SELECT COUNT(*) FROM dbo_customer_CT and your relation is correct , I did inner join to show you the rows ![alt text][1] [1]: /storage/temp/1154-lsn_time_mapping_table.jpg

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
It looks like none of your rows have aged pass the 72 hours specified by the retention column in `msdb.dbo.cdc_jobs`. The `sys.sp_MScdc_cleanup_job` procedure loops through all of the tables that are listed in `cdc.change_tables` and deletes rows from those tables that are older than `msdb.dbo.cdc_jobs.retention` in minutes (up to a maximum of `msdb.dbo.cdc_jobs.threshold` number of rows). It does this based on the transaction times recorded in `cdc.lsn_time_mapping.tran_end_time` You can learn more here (previously linked to by @sqlaj-1) - http://technet.microsoft.com/en-us/library/cc645885%28v=sql.105%29.aspx and here - http://technet.microsoft.com/en-us/library/bb500247.aspx
1 Like 1 ·
sqlaj 1 avatar image
sqlaj 1 answered
Have you read this from TechNet? Cleanup Job: http://technet.microsoft.com/en-us/library/cc645885(v=sql.105).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Troy_2012 avatar image Troy_2012 commented ·
yes,not useful
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
The cleanup job uses threshold values from msdb.dbo.cdc_jobs - maybe your data isn't outside of those thresholds? That MSDN article that @sqlaj1 linked to gives you that information
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Troy_2012 avatar image Troy_2012 commented ·
So,what happen if the job run, clean all the tables [cdc].[change_tables]. [cdc].[dbo_customer_CT]. [cdc].[ddl_history]. [cdc].[index_columns] [cdc].[lsn_time_mapping]
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
No. Some of those tables contain cdc metadata, e.g. cdc.change_tables holds details of the change tables - not the data you want to capture.
0 Likes 0 ·
Troy_2012 avatar image Troy_2012 commented ·
so is there a problem with my job ? I tried to read the SP sys.sp_MScdc_cleanup_job but I didn't understand it , what are the tables that take effect ? and clean what ? which tables are clean ?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Can you post your cdc retention configuration? SELECT * FROM msdb.dbo.cdc_jobs where job_type = 'cleanup'
0 Likes 0 ·
Troy_2012 avatar image
Troy_2012 answered
![alt text][1] [1]: /storage/temp/1153-result.jpg And my database is no.6

result.jpg (39.2 KiB)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Now, let's see how old is the oldest of the tracked rows in dbo_customer_CT: SELECT MAX(DATEDIFF(mm, tm.tran_begin_time, GETDATE())) AS OldestTrackedRow FROM cdc.dbo_customer_CT AS at LEFT JOIN cdc.lsn_time_mapping AS tm ON at.[__$start_lsn] = tm.start_lsn
0 Likes 0 ·
Troy_2012 avatar image Troy_2012 commented ·
The result is 0
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Just out of curiosity, are there any rows in dbo_customer_CT? SELECT COUNT(*) FROM dbo_customer_CT I wonder if I got the join right for sql 2012. I built the example on a sql 2008 server.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.