Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Manual cleanup Change Data Capture for a SQL Server database


By:   |   Last Updated: 2013-08-30   |   Comments (5)   |   Related Tips: 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.

How to clean up Change Data Capture Manually
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


Last Updated: 2013-08-30


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 Thank you Kun!


Friday, August 08, 2014 - 11:25:06 AM - Kun Lee Back To Top

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


Tuesday, August 05, 2014 - 11:56:52 AM - Paolo Cimini Back To Top

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! :-)

 

Regards,

 

 

Paolo


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

Very useful article!


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

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.


Learn more about SQL Server tools