-- 2021-11-08 Test script for CDC situation resulting in 2 capture instances with the same start_lsn

USE testDB;
GO

-- Insert a 2nd row
INSERT dbo.SampleTable (Name, Email) VALUES ('Name 2', 'Email2@sugarcrm.com');
GO

-- Make a schema change and Create another CI (same thing that our code does in handling a schema change)
ALTER TABLE SampleTable ADD ZipCode VARCHAR(10) NULL;
EXEC sys.sp_cdc_enable_table
     @source_schema = 'dbo'
    ,	@source_name = 'SampleTable'
    ,	@capture_instance = 'testDB_dbo_SampleTable_AfterSchemaChange'
    ,	@supports_net_changes = 0
    ,	@role_name = NULL;
GO

-- Note 2 CaptureInstances with DIFFERENT start_lsn values
EXEC sp_cdc_help_change_data_capture;
GO

-- Insert a 3rd row (with the new column)
INSERT dbo.SampleTable (Name, Email, ZipCode) VALUES ('Name 3', 'Email3@sugarcrm.com', '33333');
GO

-- See existing CDC jobs
EXEC sp_cdc_help_jobs;
GO

-- alter CDC Cleanup job to only retain data for 1 minute
EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention  = 1;
GO

-- Note new retention setting for cleanup job
EXEC sp_cdc_help_jobs;
GO

-- Wait 1 minute
WAITFOR DELAY '00:01:00';
GO

-- Start the CDC cleanup job (normally scheduled for once daily at 2AM)
EXEC msdb.dbo.sp_start_job @job_name = 'cdc.testDB_cleanup';
GO

-- Wait for it to finish
WAITFOR DELAY '00:01:00';
GO

-- Insert a 4th row
INSERT dbo.SampleTable (Name, Email, ZipCode) VALUES ('Name 4', 'Email4@sugarcrm.com', '44444');
GO

-- Wait 1 minute
WAITFOR DELAY '00:01:00';
GO

-- Start the CDC cleanup job again (normally scheduled for once daily at 2AM)
EXEC msdb.dbo.sp_start_job @job_name = 'cdc.testDB_cleanup';
GO

-- Wait for it to finish
WAITFOR DELAY '00:01:00';
GO

SELECT * FROM SampleTable AS st
    EXEC sp_cdc_help_change_data_capture;
GO

-- Insert a 5th row
INSERT dbo.SampleTable (Name, Email, ZipCode) VALUES ('Name 5', 'Email5@sugarcrm.com', '55555');
GO
