SOLVED

Possiblity of excluding CDC tables from Backup

%3CLINGO-SUB%20id%3D%22lingo-sub-3455580%22%20slang%3D%22en-US%22%3EPossiblity%20of%20excluding%20CDC%20tables%20from%20Backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3455580%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20we%20enable%20CDC%20on%20our%20database%20server%2C%20the%20size%20of%20backup%20file%20increases%20by%20a%20large%20percentage.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20ask%20if%20we%20can%20exclude%20CDC%20tables%20from%20backup%20procedure.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3458314%22%20slang%3D%22en-US%22%3ERe%3A%20Possiblity%20of%20excluding%20CDC%20tables%20from%20Backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3458314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1410167%22%20target%3D%22_blank%22%3E%40Boyee_Wu%3C%2FA%3E%26nbsp%3B%2C%20no%2C%20it's%20impossible%20to%20exclude%20anything%20from%20a%20database%20backup.%3C%2FP%3E%3CP%3EA%20backup%20is%20always%20a%20complete%20and%20exact%201%3A1%20copy%20of%20the%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOlaf%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3468681%22%20slang%3D%22en-US%22%3ERe%3A%20Possiblity%20of%20excluding%20CDC%20tables%20from%20Backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3468681%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1410167%22%20target%3D%22_blank%22%3E%40Boyee_Wu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EChange%20data%20capture%20(CDC)%20uses%20the%20SQL%20Server%20agent%20to%20record%20insert%2C%20update%2C%20and%20delete%20activity%20that%20applies%20to%20a%20table.%26nbsp%3B%3C%2FSPAN%3ECDC%20depend%20on%20the%20log%20reader%20agent%20to%20read%20the%20active%20part%20of%20the%20SQL%20Server%20Transaction%20Log%20associated%20with%20the%20changes.%20These%20SQL%20Transaction%20Log%20records%20will%20be%20in%20pending%20state%20until%20these%20tracked%20changes%20are%20replicated%20to%20the%20tracking%20tables.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20many%20cases%20the%20transaction%20logs%20are%20pending%20for%20long%20time%20and%20can%E2%80%99t%20be%20truncated%20due%20to%20slow%20log%20reader%20agent%20activities.%20This%20is%20a%20problem%20you%20may%20face%20if%20you%20configure%20SQL%20Server%20Replication%20or%20Change%20Data%20Capture%20(CDC)%20features%20in%20your%20database.%20The%20direct%20result%20is%20that%20your%20backup%20is%20bigger%20since%20you%20need%20to%20backup%20all%20these%20information.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote!%20check%20that%20the%20CDC%20capture%20job%20is%20running%20and%20tracking%20changes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20do%20not%20need%20the%20CDC%20to%20track%20old%20changes%20and%20you%20are%20OK%20with%20stop%20it%20then%20you%20can%20disable%20the%20CDC%20before%20the%20backup%20and%20enable%20it%20again%20after%20-%20obviously%20you%20will%20lose%20the%20functionality%20of%20the%20CDC%20(not%20recommended%20probably%20in%20most%20cases).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecute%20the%20following%20query%20can%20help%20find%20issues%26nbsp%3B%3CSPAN%3Edelaying%20the%20truncation%20of%20transaction%20log%26nbsp%3Bwhich%20mean%20that%20your%20full%20backup%20will%20probably%20be%20bigger.%20You%20need%20to%20monitor%20the%20reason%20for%20such%20big%20backup%20file%20since%20CDC%20by%20itself%20is%20not%20something%26nbsp%3Bthat%20must%20end%20with%20such%20behavior.%20In%20some%20cases%20you%20simply%20need%20to%20have%20more%20log%20backups%20and%20as%20mentioned%20above%20it%20can%20be%20related%20to%20an%20issue.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20name%20AS%20Database_Name%2Clog_reuse_wait_desc%0AFROM%20sys.databases%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20search%20more%20deeper%20posts%20about%26nbsp%3Blog_reuse_wait_desc%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

After we enable CDC on our database server, the size of backup file increases by a large percentage.

I would like to ask if we can exclude CDC tables from backup procedure.

Thanks.

2 Replies

@Boyee_Wu , no, it's impossible to exclude anything from a database backup.

A backup is always a complete and exact 1:1 copy of the database.

 

Olaf

best response confirmed by Boyee_Wu (Visitor)
Solution

Hi @Boyee_Wu 

 

Change data capture (CDC) uses the SQL Server agent to record insert, update, and delete activity that applies to a table. CDC depend on the log reader agent to read the active part of the SQL Server Transaction Log associated with the changes. These SQL Transaction Log records will be in pending state until these tracked changes are replicated to the tracking tables.

 

In many cases the transaction logs are pending for long time and can’t be truncated due to slow log reader agent activities. This is a problem you may face if you configure SQL Server Replication or Change Data Capture (CDC) features in your database. The direct result is that your backup is bigger since you need to backup all these information.

 

Note! check that the CDC capture job is running and tracking changes.

 

If you do not need the CDC to track old changes and you are OK with stop it then you can disable the CDC before the backup and enable it again after - obviously you will lose the functionality of the CDC (not recommended probably in most cases).

 

Execute the following query can help find issues delaying the truncation of transaction log which mean that your full backup will probably be bigger. You need to monitor the reason for such big backup file since CDC by itself is not something that must end with such behavior. In some cases you simply need to have more log backups and as mentioned above it can be related to an issue.

 

SELECT name AS Database_Name,log_reuse_wait_desc
FROM sys.databases
GO

 

 

You can search more deeper posts about log_reuse_wait_desc