Unable to truncate transactionlog with CDC

· Uncategorized

The other day I ran into following problem; A DB which was CDC enabled, started to fill up the transaction log to an extent where a translog backup could not run due to lack of space.

So I performed a remote tranlog backup (using UNC path as destination for my backup file), in order for the Tranlog file to be shrunk.

No effect.

So I waited for the capture process to finish, even using much more Tranlog space, so no new records would be captured. Re-executed tranlog backup, checked DBCC SQLPERF(LOGSPACE); no free space.

Also other actions had no effect:

–          Put DB in simple recovery mode

–          Execute tranlog backup and shrink multiple times

–          Stop SQL Agent to make sure no more transactions would be started

–          Restart SQL Server service

DBCC LOGINFO showed the VLF’s remained active.

This is how I solved it:

  1. Detach the DB
  2. Restore the DB using the FOR ATTACH_REBUILD_LOG option. A new empty tranlog file is created
  3. Enable the DB for CDC
  4. Run exec sys.sp_cdc_add_job ‘capture’
  5. Run exec sys.sp_cdc_add_job ‘cleanup’

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: