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.
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:
- Detach the DB
- Restore the DB using the FOR ATTACH_REBUILD_LOG option. A new empty tranlog file is created
- Enable the DB for CDC
- Run exec sys.sp_cdc_add_job ‘capture’
- Run exec sys.sp_cdc_add_job ‘cleanup’