Recently we faced a critical issue on production. The transaction log drive was full and the database was not ready to take any new connection from application impacting the site going down.
What did we do to resolve this issue
We checked the sys.databases view and found the log_reuse_wait_desc showing us REPLICATION.
We tried to initiate the tlog backup but this does not release any space to disk as it was impacting the production we quickly added secondary tlog file on a separate drive and the site was up. This reduced the criticality of the situation.
But we have to control the tlog growth which was now filling up the second disk. We script out the replication and dropped it this cleared the unreplicated the transactions from the database once we controlled the growth and shrinked the tlog file we were able to empty the new tlog file and was in control of the situation.
What we learned from this issue
- When the log_reuse_wait_desc showing REPLICATION, check if the log reader agent is running or not, if not running then run it
- If you do not have any transactions or you are okay to reset or clear all the pending transactions possible run the command EXEC sp_repldone
- In our case, we simply scripted the replication dropped it and reconfigure it and reinitialized it. This was decided as we need to remove the second tlog file and shrink the tlog.
Happy Learning ….