hi all,

from time to time we will see a CDC (change data capture) in MSSQL, when running with replication, around every 6 months, will make the user database's log file growth too large and can't come down in size any more.

the pattern is :
1) whenever the SQL job csc.<user database>_capture job STOPPED, this is a clear indicator that something went wrong, the <user database> log file size will be very large.
2) we then verify if it is the problem by running this:

select log_reuse_wait_desc,name, log_reuse_wait from sys.databases;

Select allOpen in new window

we should then we in the log_reuse_waiting_desc column, next to our user database, will show REPLICATION instead of ACTIVE_TRANSACTION.

3) we will see if the user database log size increase dynamically to make log disk full.
4) we also run this:

DBCC SQLPERF(logspace)

Select allOpen in new window

to see "log Space Used [%]" if it is 99% >.

5) we will finally use

 DBCC OPENTRAN(<user database>) 

Select allOpen in new window

to see if the oldes non-distributed LSN of a transaction has been hold there for a long time and number never change:

Oldest active transaction:
    SPID (server process ID): 158
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (631159:27303:3)
    Start time    : Jun 25 2015  5:40:47:817AM
    SID           : 0x010500000000000515000000c5369faad8bb12aa5e06187c0d040000
 
Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : ([b]631159:25862:1[/b])
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Select allOpen in new window

, then we know there is the CDC and replication both work together to make log size can't come down at all.

solution:

we can only:
1) Make sure that the CDC capture job method above can start again, once start it will keep starting and run by itself.
2) we will run this:

sp_replflush

sp_repldone null, null, 0,0,1

sp_replflush

Select allOpen in new window

3) once it is flushed, we can the shrink  the log by release space only.

this is how we due with it but how you guys handle it in a much robust way ? can automate the whole solution so we this problem will be fixed when system detect it ?