USE [master] GO -- ============================================= -- Author: Emil Bialobrzeski -- Create date: 16/09/2016 -- Description: Procedure is looking for all databases that are -- part of an availibility group. -- If the group has a PRIMARY role and the database has cdc enabled -- procedure will check if there is a CDC job already created for this database. -- If the job does not exist, procedure will create one. -- If the job exists, procedure will check in what state the job is. -- If the job is running procedure will do a RETURN and nothing will happen. -- IF the job is not running procedure will start it. -- ============================================= ALTER PROCEDURE [dbo].[sp_cdc_hadr_watchdog] AS BEGIN
DECLARE cdc_watchdog_dbs CURSOR FAST_FORWARD READ_ONLY FOR SELECT D.name FROM sys.dm_hadr_availability_replica_states AS A JOIN sys.availability_replicas AS B ON B.replica_id = A.replica_id JOIN sys.availability_groups AS AG ON AG.group_id = A.group_id JOIN sys.availability_databases_cluster AS ADC ON ADC.group_id = A.group_id JOIN sys.databases AS D ON D.name = ADC.database_name AND D.is_cdc_enabled = 1 WHERE B.replica_server_name = @@SERVERNAME AND A.role_desc = 'PRIMARY'
OPEN cdc_watchdog_dbs
FETCH NEXT FROM cdc_watchdog_dbs INTO @database_name
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = ' USE ['+@database_name+'];
IF OBJECT_ID(''tempdb..#xp_results'') IS NOT NULL EXEC (''DROP TABLE #xp_results'')
IF OBJECT_ID(''tempdb..#CDCJobs'') IS NOT NULL EXEC (''DROP TABLE #CDCJobs'')
SELECT @job_owner_capture = SP.name, @job_id_capture = S.job_id FROM #CDCJobs AS CJ JOIN msdb.dbo.sysjobs AS S ON s.name = CJ.job_name JOIN sys.server_principals AS SP ON SP.sid = S.owner_sid WHERE CJ.job_type = ''capture''
SELECT @job_id_cleanup = CJ.job_id FROM #CDCJobs AS CJ WHERE CJ.job_type = ''cleanup''
IF @job_id_capture IS NULL EXEC sys.sp_cdc_add_job ''capture''
IF @job_id_cleanup IS NULL EXEC sys.sp_cdc_add_job ''cleanup''
IF @job_id_capture IS NOT NULL BEGIN CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL)
INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs 0, @job_owner_capture, @job_id_capture
SELECT @is_running = XR.running FROM #xp_results AS XR
