topic SQL Server LOG_BACKUP full in Qlik Replicate

SQL Server LOG_BACKUP full

JacobTews — Wed, 08 May 2024 15:33:38 GMT

We run Replicate and Compose on-prem, writing to a SQL Server instance also on-prem. Our DBA has scheduled 4-hour backups for all the company's SQL Servers which is supposed to truncate the transaction logs for all the databases after doing so. However, the logs for both my test and production data warehouse databases seem to be immune to such truncation:

Notice in particular the fact that the log for the test data warehouse is actually bigger than the test data warehouse itself!

Any systems-knowledgeable folks out there have any insights?

Is there a setting in Replicate that I have inadvertently toggled to cause such behavior?

Re: SQL Server LOG_BACKUP full

JacobTews — Wed, 08 May 2024 15:35:01 GMT

Just heard from the DBA, who tested a shrink operation and had success:

Seems like maybe this is actually a SQL Server question, not a Replicate one, but if anyone has any thoughts, I'd still like to hear them. Thanks!

Re: SQL Server LOG_BACKUP full

PGN — Wed, 08 May 2024 17:24:14 GMT

Is the target in Simple mode or Full/Bulk-logged mode? If it's a target using Replicate to populate the table, I'm not sure I'd keep it in Full mode since you can recover any data loss by rerunning the transactions in Replicate (especially if the target isn't running Tlog backups). This most likely is the reason why the transaction logs are growing.

You don't say what kind of backups are scheduled (full, transactional, etc.). If the target is in Full recovery mode, then I'd suggest running periodic tlog backups to maintain the size. Or, just run it in simple mode and shrink the tlog periodically if the size is an issue. The shrink (or truncation) will only be effective if all of the transactions are committed (in simple mode) or committed and backed up (in full/bulk-log mode). The caution about shrinking tlogs is that they may cause some system overhead when they grow, because they will grow again, and the only benefit is reclaiming disk space. But again, they will just grow back.

Re: SQL Server LOG_BACKUP full

SushilKumar — Thu, 09 May 2024 01:18:26 GMT

Hello @JacobTews

@PGN Is correct. Target database act a normal database where Qlik replicate works as a normal Application user it will connect and execute the DML received from QR engine . Tlogs/backup logs housekeeping impact Qr working when SQL server used as a Source.

So you can have you Tlog backup more frequently and reduce the Tlog/backup logs retention shorter then the Source Sql server.

Regards,

Sushil Kumar

When SQL server is used as Target.