Hi, we have a problem where log size of a database is very high. It turns out its waiting for replication. Found this info by using below SQL script in the SQL Server. SELECT log_reuse_wait_desc FROM sys.databases
This Qlik replication have been working fine for about a year now without issue. Why it is happening now?
Previously we did faced this issue, that time we resolved it by removing the replication publisher in the SQL Server (AlwaysOn), take a log backup, then the log size is reduced before we re-add back to the Qlik replication. But now business want to avoid removing this publisher to fix this issue and want to look other best way to fix this log issue. Please assist how we can achieve this.
This happens to my system occasionally. Though not as much since we started using logstream. If you do a DBCC Opentran, it should show what tasks are "stuck." Stop the task(s) and then double-check the log-reader jobs on your distribution server. If they're in retry, check the error there. If they're stopped, then they should be running to clear out the tlog. If they're running, but the log won't free up space, stop them and run the following commands:
EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1; go sp_replflush go --sp_replrestart Not needed all the time
These should be run on the publication server in the DB that's replicated.
You may have to run them a few times. Eventually, the SELECT log_reuse_wait_desc FROM sys.databases should show either "log backup" (which means you should run a log backup and then shrink the log if you want to reclaim the space). And then, it should show "nothing."
It's important that the log reader jobs run, otherwise the system won't let the space get free once the transactions are committed.
Dropping publications isn't needed, but really doesn't cause too much of an issue since Qlik creates them when it starts if they don't exist. But that may lead to orphan log reader jobs.
