When an SQL Server RDS instance provided by Amazon is configured for a recovery model using Full transaction logging, will the transaction log be trimmed by the "transaction log capture" that Amazon performs every 5 minutes, or is this capture not a true transaction log backup?


2 Answers 2


Almost certainly it does trim the transaction log. (Otherwise the Amazon cloud will fill up with transaction logs.)

Some details from the Amazon site include this topic and two sub-topics that you can investigate: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those that are used for backups (Amazon RDS manages backups for you).

For a discussion of the backup model and how you influence it, see: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html

And, of course, if you change the model from FULL to SIMPLE, the retention period, and so forth that will affect your restore points.

Amazon definitely support point-in-time restores, which means that the transaction logs are being used. But since they do this for all database server types that they support, I do not know the mechanics of what they use to do this.

On the restore process described here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

When you restore a DB instance that is using the SQL Server DB engine to a point in time, each database within that instance will be restored to a point in time within 1 second of each other database within the instance. Transactions that span multiple databases within the instance may be restored inconsistently.

So I would say that, whatever the mechanism of the log backup, it will be available within the constraints discussed in their documentation.

  • yes it does and it depends on the retention policy which is user configurable Amazon RDS retains backups of a DB Instance for upto the last 35 days (configurable)
    – Kin Shah
    Sep 5, 2013 at 15:04

From, Amazon RDS FAQs, the "transaction log capture" will allow you to do a point-in-time recovery of your database.

The automated backup feature of Amazon RDS enables point-in-time recovery of your DB Instance. When automated backups are turned on for your DB Instance, Amazon RDS automatically performs a full daily snapshot of your data (during your preferred backup window) and captures transaction logs (as updates to your DB Instance are made). When you initiate a point-in-time recovery, transaction logs are applied to the most appropriate daily backup in order to restore your DB Instance to the specific time you requested. Amazon RDS retains backups of a DB Instance for a limited, user-specified period of time called the retention period, which by default is one day but can be set to up to thirty five days. You can initiate a point-in-time restore and specify any second during your retention period, up to the Latest Restorable Time.

  • I've read that description but was uncertain if what Amazon calls "log capture" has exactly the same semantics as a log backup (incl. leading to the log being trimmed; my key concern here) as described in an SQL Server context.
    – Cumbayah
    Sep 5, 2013 at 19:13
  • @Cumbayah It is exactly what it says. It will allow you to do a point-in-time recovery as well it has a limit of 35 days that you can keep worth of. So depending on your need, you can trim/get rid of older ones.
    – Kin Shah
    Sep 5, 2013 at 19:14
  • I have switched backup completely off and I still see there are ~ 7MB/s of transaction log generation, which burned ~ 240 write IOPS.
    – tom10271
    Aug 27, 2018 at 2:37

Your Answer

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

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