Script to completely Disable and Remove CDC

Hello folks,

I believe most of us are having problems with stuburn CDC and would like to get rid of it someday somehow! Well, say no more! Usually after a srestore, CDC gets stuck and doesn’t behave properly but disabling using the old fashioned way doesn’t work either.

So  here’s a complete script how to handle such a disastrous problem 🙂

EXEC sys.sp_cdc_disable_db 
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test;
GO
IF OBJECT_ID('tempdb..#CDCTransac') IS NOT NULL
DROP TABLE #CDCTransac;
GO
CREATE TABLE #test(status NVARCHAR(255));
INSERT INTO #test(Status)
EXEC master.dbo.xp_servicecontrol
'QueryState',
'SQLServerAgent';
IF NOT EXISTS
(
SELECT TOP 1 1
FROM #test AS t
WHERE t.status LIKE '%run%'
)
BEGIN
EXEC master.dbo.xp_servicecontrol
'start',
'sqlserveragent';
END;
GO
IF NOT EXISTS
(
SELECT job_id
FROM msdb.dbo.sysjobs_view
WHERE name = 'cdc.test_cleanup'
)
BEGIN
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup';
END;
GO
IF EXISTS
(
SELECT job_id
FROM msdb.dbo.sysjobs_view
WHERE name = N'cdc.test_capture'
)
BEGIN
EXEC msdb.dbo.sp_delete_job
@job_name = N'cdc.test_capture',
@delete_unused_schedule = 1;
END;
GO
IF EXISTS
(
SELECT TOP 1 1
FROM msdb.dbo.cdc_jobs AS CJ
)
BEGIN
DELETE FROM msdb.dbo.cdc_jobs;
END;
GO
DECLARE @tableName NVARCHAR(100);
DECLARE CDCCleanupCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT QUOTENAME(t.name) AS name
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'cdc';
OPEN CDCCleanupCursor;
FETCH FROM CDCCleanupCursor INTO @tablename;
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC ('drop table cdc.'+@tableName+'; ');
PRINT 'drop table cdc.'+@tableName+'; ';
FETCH NEXT FROM CDCCleanupCursor INTO @tableName;
END;
CLOSE CDCCleanupCursor;
DEALLOCATE CDCCleanupCursor;
GO
DECLARE @prName NVARCHAR(100);
DECLARE CDCCleanUpCursor2 CURSOR FAST_FORWARD READ_ONLY
FOR SELECT QUOTENAME(pr.name) AS name
FROM sys.procedures AS pr
JOIN sys.schemas AS s ON pr.schema_id = s.schema_id
WHERE s.name = 'cdc';
OPEN cdccleanupcursor2;
FETCH FROM cdccleanupcursor2 INTO @prName;
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC ('drop procedure cdc.'+@prName+'; ');
PRINT 'drop procedure cdc'+@prName+'; ';
FETCH NEXT FROM CDCCleanUpCursor2 INTO @prName;
END;
CLOSE CDCCleanUpCursor2;
DEALLOCATE CDCCleanUpCursor2;
GO

DECLARE @fnName NVARCHAR(100);
DECLARE CDCCleanUpCursor3 CURSOR FAST_FORWARD READ_ONLY
FOR SELECT QUOTENAME(fn.name) AS name
FROM sys.objects AS fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
WHERE fn.type IN('FN', 'IF', 'TF')
AND s.name = 'cdc';
OPEN CDCCleanUpCursor3;
FETCH FROM CDCCleanUpCursor3 INTO @fnName;
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC ('drop function cdc.'+@fnName+'; ');
PRINT 'drop function cdc'+@fnName+'; ';
FETCH NEXT FROM CDCCleanUpCursor3 INTO @fnName;
END;
CLOSE CDCCleanUpCursor3;
DEALLOCATE CDCCleanUpCursor3;

DECLARE @ruleName NVARCHAR(100);
SELECT @ruleName = DP1.name
FROM sys.database_principals AS dp1
JOIN sys.database_principals AS dp2 ON dp1.owning_principal_id = dp2.principal_id
WHERE dp1.type = 'R'
AND dp2.name = 'cdc';
IF @ruleName IS NOT NULL
EXEC ('ALTER AUTHORIZATION ON ROLE::'+@ruleName+' TO dbo; ');
PRINT('ALTER AUTHORIZATION ON ROLE::'+@ruleName+' TO dbo; ');
GO
IF EXISTS
(
SELECT *
FROM sys.schemas AS s
WHERE s.name = N'cdc'
)
DROP SCHEMA [cdc];
GO
IF EXISTS
(
SELECT TOP 1 1
FROM sys.Database_principals AS dp
WHERE dp.name = N'cdc'
)
BEGIN
DROP USER [cdc];
END;
GO
SELECT @@TRANCOUNT;
DECLARE @ExecFixTransacSQL NVARCHAR(MAX);
DECLARE @id INT;
DECLARE @numberoftransactions INT;
DECLARE @DisableCDC NVARCHAR(MAX)= 'EXEC sys.sp_cdc_disable_db';
EXEC SP_EXECUTESQL
@DisableCDC;
SELECT @@tRANCOUNT AS NumberOfTransactions,
ID = IDENTITY( INT, 1, 1)
INTO #CDCTransac;
IF @@ERROR != 0
BEGIN
RETURN;
END;
ELSE
SELECT @numberoftransactions = ct.numberoftransactions,
@id = ct.id
FROM #CDCTransac cT;
SET @ExecFixTransacSQL = 'COMMIT TRANSACTION';
PRINT @numberoftransactions;
WHILE 1 < @numberoftransactions
BEGIN
IF @@TRANCOUNT = 0
BEGIN
RETURN;
END;
PRINT @ExecFixTransacSQL;
EXEC SP_EXECUTESQL
@ExecFixTransacSQL;
SET @numberoftransactions = @numberoftransactions - 1;
END;

Hope that helps someone out there whom life is being negatively influenced by such a bug 😦

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s