0

With AWS RDS being a managed service it manages backs and other aspects itself (i.e. no permissions to back up log / truncate manually) - how and when AWS RDS is truncating the transaction log?

I have a MSSQL database with couple read replicas in AWS RDS and the transaction log is growing in size that is causing dramas sometimes.

I can't change the recovery model, cant do backup log, etc. AWS backup SP (msdb.dbo.rds_backup_database) is taking a copy and does not truncates the log.

Just trying to understand how is it supposed to work.

log_reuse_wait_desc shows for the database REPLICATION status. I am guessing the REPLICATION status means data being sent to read replicas, but not sure why this is happening constantly and the log_reuse_wait_desc does not change

Improve this question
3
  • 1
    Your log_reuse_wait_desc says you explicitly that log backup operations is not an issue. Before truncating the log SQL must ensure all transactions in truncating part are delivered to subscribers.
    – Denis Rubashkin
    Nov 4, 2022 at 10:23
  • Are you using Replication?
    – J.D.
    Nov 4, 2022 at 12:51
  • >Are you using Replication? I would think so - the main DB has 5 read replicas
    – Dmitry
    Nov 7, 2022 at 2:57

1 Answer 1

Reset to default
2

[...] how and when AWS RDS is truncating the transaction log?

Amazon doesn't have any hooks into SQL Server that could truncate the log as this is an internal process to SQL Server. Thus, it'll follow the typical log trunction path, as linked above, depending on recovery model which I assume is forced to full. This means, assuming no holdups, the log will attempt to be truncated as each log backup is finished. How often are log backups taken seems to depend on how AWS decides to take them but can be as frequent as every 5 minutes.

[...] log_reuse_wait_desc shows for the database REPLICATION status.

That is the truncation holdup reason, replication is configured and is the long pole in the tent.

From the documentation:

REPLICATION - During transactional replications, transactions relevant to the publications are still undelivered to the distribution database. (Full recovery model only). For information about transactional replication, see SQL Server Replication.

Clearly replication is configured, which has nothing to do with availability groups as it's a completely seperate technology in SQL Server. If you're using replication, you'll need to troubleshoot why it's lagging behind. If you're not using replication, turn it off.

Improve this answer
4
  • Thank you for the reply. There are no replication lag shown in AWS RDS. >If you're not using replication I am not sure here. We have a writer instance and a couple of reader instances configured via AWS RDS.
    – Dmitry
    Nov 8, 2022 at 3:37
  • Since the wait is replication, I can tell you it's enabled. No such thing as writer and reader instances for SQL. You might have multiple replicas (AG) which has nothing to do with Replication. I don't know if you are or are not using Replication, though, but the post stands and gives you what you should need... whether you know what you are using or not is another story and I can't tell you that since I don't work at your company and know your applications.
    – Sean Gallardy
    Nov 10, 2022 at 14:54
  • It looks like CDC is the the reason why transaction log is not truncating
    – Dmitry
    Dec 30, 2022 at 0:22
  • Looks like sys.databases wasn't lying to you :)
    – Sean Gallardy
    Dec 30, 2022 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.