Ghost Replication and the Growing Log File2 min read

I know it is a weird time to be putting out a blog post.  But I have 28 hours to keep my goal of 52 blogs in 52 weeks going.  You say it is only week two and you are this far behind.  This week was hectic since I was on-call at work.

So I thought what a perfect time to try and get this knocked out.  Put my son to sleep, my wife is out of town, I found some ice cream in the freezer and football is on TV.  Sounds like the perfect blog writing atmosphere.  Oh, and I had a weird issue pop up at work this week so I have something to write about.

Problem

We have a central administration server where we run data captures and alerting for our entire infrastructure.  A couple weeks ago our monitoring tool Spotlight threw a low level alert about the log drive on this admin server.  And being the good DBA I am, I saw the alert each morning when checking things, but did not pay too much attention since it was a DBA Team server.  On Monday of this past week the alert had grown from yellow (low level alert) to a red (high level alert).

I took the following troubleshooting steps:

1. Verify the Transaction Log backup job was running successfully
2. Checked the recovery model for the database in question
3. It is set to SIMPLE, so what is causing this log to grow
4. I queried sys.databases  and found that REPLICATION was the current log_reuse_wait_desc

What!!!  We do not have replication on this server.  Ok, someone is messing with me.

Further digging and googling pointed at CDC.  Well this is not something we have used in the past.  However, I did recall one of my colleagues talking about using CDC to capture table changes for auditing purposes.  But I had never heard that he moved forward.

So sure enough, I queried sys.databases again and the is_cdc_enabled  flag was true for this database.  Further investigation found that the cdc.DBName_capture job had failed and then was disabled.  Well that is not good as everything was being jammed in the transaction log waiting to be saved to the CDC tables.

Solution

I talked to my colleague who had abandoned the idea of seeing why the job was failing and using CDC all together.  Needless to say he did not know that this would be a result, nor did I when it was discussed.

So I ran the command below to disable CDC for the database involved.

Once I did that I was able to shrink the transaction log back to a normal size.

Conclusion

To wrap this up I would say that if you are testing a new technology on a live server be a little more cognizant when you see things that do not look right.  And stay on top of your alerts!!!