Manual cleanup Change Data Capture for a SQL Server database

By:   |   Comments (5)   |   Related: More > Database Administration


I had a database where the log file kept growing and used 99.99% of the available space.  The database recovery model was set to "SIMPLE" and there was no replication setup for this database, but the transaction log kept growing.  The next thing I looked at was Change Data Capture (CDC) to see if that was the issue.  I ran the query below to see if CDC was enabled for this database, but it didn't return anything.  I was still having issues with this database and I noticed miscellaneous change data capture objects still in the database as well as open transactions.  This was causing my transaction log to continue to grow, but I couldn't disable CDC, because SQL Server thought it was not enabled.

How to clean up Change Data Capture Manually

If the log file keeps growing, typically, it is due to an OPEN TRANSACTION, the database is replicated and there is an issue with replication or Log Backups aren't running if the database is in FULL recovery mode. In this case, since the database was in SIMPLE recovery mode, I followed the below steps to get this resolved. I also ran "DBCC SQLPERF(LOGSPACE)" in a query window to see how much log space was being used.

Before you follow these steps, I highly recommend doing this in a Test environment if the database you are working on is a Production database. You can read this article Restoring a SQL Server database that uses Change Data Capture to restore the database to another environment.  You can download the scripts here.

Step 1 - Look for Open Transactions

The first step in my process was to see if there were any open transactions in the database.  I ran the following code:

DBCC OPENTRAN -- Confirm to see if there is any open transaction

The result is below:

Transaction information for database 'MyDB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (6627682:6063:2)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you can see above, I have an open transaction in the database.

Since my database was not setup for replication and I found CDC objects in the database I want to try to disable CDC to see if that cleans up the issue.

I ran the following command in the database to turn off CDC.

exec MyDB.sys.sp_cdc_disable_db

After running this, I got the below error because CDC is actually disabled.

"The database 'MyDB' is not enabled for Change Data Capture. Ensure that the 
correct database context is set and retry the operation. To report on the 
databases enabled for Change Data Capture, query the is_cdc_enabled column 
in the sys.databases catalog view."

Step 2 - Check for CDC Objects

So, if you get the above error, you can run the query below to see if there are any CDC related objects that still exist.

-- Check any CDC related objects exists
/* ===========================================================================*/
use MyDB
-- Check CDC Tables
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE = 'cdc'

-- Check CDC procs
FROM sys.procedures pr
JOIN sys.schemas s ON pr.schema_id = s.schema_id
WHERE = 'cdc'
-- Check any CDC functions, triggers, etc.
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
WHERE fn.type IN ( 'FN', 'IF', 'TF' )
AND = 'cdc'
-- Check prinipals
SELECT @ruleName =
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 = 'cdc';
-- Check schemas
SELECT * FROM sys.schemas WHERE name = N'cdc'
-- Check user CDC
SELECT * FROM sys.database_principals WHERE name = N'cdc'
-- Check CDC jobs
SELECT name FROM msdb..sysjobs where name like 'cdc.%'

Step 3 - Remove Old CDC Objects

Based on above query, if it returns any objects these should be dropped.  You can use this script that was developed by some of my colleagues.  This script finds any of these old CDC objects and removes them.  You can find the script here. The idea is to make sure that there are no more CDC objects left in the database.

NOTE: If you have database triggers that prevent dropping system objects you will need to disable the triggers first.

Step 4 - Enable CDC and Disable CDC

Since we did a manual cleanup of the objects,  it is highly recommended to enable CDC and then disable CDC again to allow SQL Server to do a complete cleanup of the CDC objects and settings. Once that is done, you can check again for any open transactions.


I hope this helps you do a manual cleanup of CDC if this is causing issues with your transaction log size. After you have things working again, you may want to shrink the log file to the proper size to get back some disk space.

*NOTE: If you are working on a critical production database, I would contact Microsoft support for assistance. Also, I would make sure you have good backups for any manual cleanups.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 14, 2017 - 11:30:09 AM - Tim De Back To Top (74011)

 Thank you Kun!

Friday, August 8, 2014 - 11:25:06 AM - Kun Lee Back To Top (34062)

Thanks all!! I am so glad that it helped Palolo!

Tuesday, August 5, 2014 - 11:56:52 AM - Paolo Cimini Back To Top (34010)

Thank you all very much for these scripts!


They saved me a lot of troubles and work, I was able to clean my production database from the CDC leftovers and enable the service again! I was really in trouble and you basically saved my from a lot of problems.


Thanks again, really helpful and well explained! :-)






Thursday, February 13, 2014 - 1:08:41 PM - Csaba Toth Back To Top (29433)

Very useful article!

Thursday, December 12, 2013 - 3:44:39 AM - Harshavardan.R Back To Top (27775)

Subject:- Convert .ndf file into .mdf 

Scenario:- Due to some groove disk cleanup mdf file is deleted but on safe I have ndf file how to convert it as primary mdf file and bring DataBase online.

Note:- No backup avilable only ndf file is present.

get free sql tips
agree to terms