Manual cleanup Change Data Capture for a SQL Server database

By: Kun Lee   |   Updated: 2013-08-30   |   Comments (5)   |   Related: More > Database Administration

Problem

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.

Solution

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:

USE MyDB

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

go

-- Check CDC Tables
SELECT MyDBname
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'cdc'

-- Check CDC procs
SELECT QUOTENAME(pr.name) AS name
FROM sys.procedures pr
JOIN sys.schemas s ON pr.schema_id = s.schema_id
WHERE s.name = 'cdc'

-- Check any CDC functions, triggers, etc.
SELECT QUOTENAME(fn.name) AS name
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
WHERE fn.type IN ( 'FN', 'IF', 'TF' )
AND s.name = 'cdc'

-- Check prinipals
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';

-- 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.

DONE!

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

• How to shrink log files and check VLFs for best performance after log file size shrunk.

About the author

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