This job doing what - sys.sp_MScdc_cleanup_job ?

Question

question

Troy_2012 asked Nov 5, '13

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-2012 change-data-capture

10 |1200

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

Answer 1 · 2013-11-05T19:02:09Z

Troy_2012 answered Nov 5, '13

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

lsn_time_mapping_table.jpg (124.5 KiB)

1

10 |1200

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

KenJ commented · Nov 05, 2013 at 08:29 PM

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 ·

Answer 2 · 2013-11-05T13:33:13Z

sqlaj 1 answered Nov 5, '13

Have you read this from TechNet? Cleanup Job: http://technet.microsoft.com/en-us/library/cc645885(v=sql.105).aspx

1

10 |1200

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

Troy_2012 commented · Nov 05, 2013 at 01:59 PM

yes,not useful

0 ·

Answer 3 · 2013-11-05T14:15:17Z

Kev Riley answered Nov 5, '13

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

10 |1200

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

Troy_2012 commented · Nov 05, 2013 at 02:25 PM

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 ·

Kev Riley ♦♦ commented · Nov 05, 2013 at 02:38 PM

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 ·

Troy_2012 commented · Nov 05, 2013 at 03:00 PM

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 ·

KenJ commented · Nov 05, 2013 at 03:58 PM

Can you post your cdc retention configuration? SELECT * FROM msdb.dbo.cdc_jobs where job_type = 'cleanup'

0 ·

Answer 4 · 2013-11-05T16:38:11Z

Troy_2012 answered Nov 5, '13

![alt text][1] [1]: /storage/temp/1153-result.jpg And my database is no.6

result.jpg (39.2 KiB)

3

10 |1200

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

KenJ commented · Nov 05, 2013 at 04:49 PM

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 ·

Troy_2012 commented · Nov 05, 2013 at 05:01 PM

The result is 0

0 ·

KenJ commented · Nov 05, 2013 at 05:16 PM

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 ·

question