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

  1. When the log_reuse_wait_desc showing REPLICATION, check if the log reader agent is running or not, if not running then run it
  2. 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
  3. 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 ….

Categories: Uncategorised

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Uncategorised

10 reasons to move to SQL Server 2019

Read more in the link- Learn more about SQL Server 2019 by downloading the data sheet: http://download.microsoft.com/download/D/2/5/D2519504-0ACD-4CD7-9C34-AB85D5824F34/SQL_Server_2019_Top_10_Reasons_to_Choose_Infographic_EN_US.pdf

Uncategorised

Replication using Remote distributor on ALWAYS ON AVAILABILITY GROUP

In SQL Server, Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It is highly recommended to Read more…

Uncategorised

Configuring transnational replication on Always on availability group SQL Server 2014

SQL Server replication supports the automatic failover of the publisher, the automatic failover of transactional subscribers, and the manual failover of merge subscribers. The failover of a distributor on an availability database is not supported. In Read more…

000webhost logo