Welcome to Monday Morning Madness. What do you do when your Transaction Log runs out of space? Let me share my most recent experience.

My transaction log had a hard limit of 2TB in size. There are different opinions out there among the DBA elite on whether you should have a limit on the growth of your transaction log or if you should leave it as unlimited. As a BI Developer I do not have an opinion here, just note that I am happy that in this instance I had a hard limit.

The Error Message:

The transaction log for database <Database Name> is full due to ‘REPLICATION’.

Oh that’s it?? Easy fix right. Let’s walk through troubleshooting this problem.

• My database is in SIMPLE recover mode, so why is it waiting to checkpoint?
• I run the following command to find out the reason the log is waiting to clear.

USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = '<Database Name>';

Replication, really? Who turned on REPLICATION!!?? I had no idea replication was turned on. Let’s find out.The following code will show if replication is turned on for any of my databases:

SELECT name, is_published, is_subscribed, is_merge_published, is_distributor
FROM sys.databases
WHERE    is_published = 1 or is_subscribed = 1 or
        is_merge_published = 1 or is_distributor = 1

Ok so here is my result set from the above query:

• So to recap, the transaction log is not purging itself because of a long open transaction due to replication but replication is not turned on?? DBCC OPENTRAN will show open transactions.

Well after some further research I discovered that my Change Data Capture job had failed. More specifically the CDC_Capture job. We turned on CDC to capture some otherwise hard to capture changes. The way CDC works is it crawls the transaction log and once it has completed with a section of the log that part of the log can then be cleared and reused. In my case CDC job failed and as a result none of the transaction log was being allowed to clear.

Note: By default when you enable CDC it will create two jobs. One that captures the changes and the other that cleans up the tables where the changes are stored. The schedule to run these jobs is configured to ONLY start upon starting of SQL Agent. I would recommend modifying the scheduling of this job.

So finally we discovered the problem. Now how do we fix this?

1. First try turning on the CDC Capture job. (This doesn’t work because the transaction log is full.)
2. Try shrinking the database. (Well this won’t work for two reasons. One transaction log is full, secondly because there is no free space to shrink.)
3. Third you can try changing the max limit on your transaction log. Bet you can’t guess what happens when you do this? This doesn’t work because the transaction log is full.

None of the above situations will work here because they all require at least some space on the transaction log to complete.

1. Ultimately I ended up creating a new transaction log on my database.
   1. This is why I was glad my original transaction log had a “Hard” limit. If there was no hard limit then I ultimately would have completely run out of space on my disk and I would not have been able to simply create a second transaction log.
2. Once the new transaction log was in place I then disabled CDC on the database. I disabled it because the client was no longer using it. Alternatively I could have turned on the job and just let it crawl the entire transaction log, all two TB .
3. Once CDC was disabled I ran the following checkpoint command to checkpoint and clear the transaction log. (See below).
4. Once the transaction log was cleared I ran DBCC SHRINKFILE (DatabaseName_LOG) to reclaim the empty space.
5. Finally I deleted the backup transaction log from above!
   1. In general having more than one transaction log can hurt performance. I added the additional transaction log temporarily and once it was no longer needed I removed it.

6. USE <DatabaseName>;
   GO
   CHECKPOINT;
   GO
   CHECKPOINT; -- run twice to ensure file wrap-around
   GO

As Always thanks for looking!