April 26, 2018 at 5:31 am
Perhaps I am missing something 'Simple'...
I am running an instance on AWS RDS..
I am trying to set the recovery mode of a database to simple eg:
ALTER DATABASE [Test] SET RECOVERY SIMPLE
his works fine, and I can see the change BUT, after a few minute it reverts back to FULL?? Am I missing something?
April 26, 2018 at 5:34 am
Perhaps this might help: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.DatabaseRecovery.html
Specifically:
Changing a database’s recovery model to “Simple” while backup retention is enabled will result in Amazon RDS setting the recovery model to “Full” within five minutes of the setting change. This will also result in Amazon RDS taking a snapshot of the DB instance.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2018 at 5:45 am
sharky - Thursday, April 26, 2018 5:31 AMPerhaps I am missing something 'Simple'...I am running an instance on AWS RDS..
I am trying to set the recovery mode of a database to simple eg:
ALTER DATABASE [Test] SET RECOVERY SIMPLEhis works fine, and I can see the change BUT, after a few minute it reverts back to FULL?? Am I missing something?
RDS is supposed to be fully managed service, why would you want to change the recovery model when you cannot control or manage it?
😎
The only thing you could possibly accomplish would be breaking the log chain!
April 26, 2018 at 6:25 am
Thom, Erikur, Thanks for the help.. Understand a bit more how AWS works.. thanks.. The problem was that the log was growing to a few gig (log_Reuse_Wait_description = LOG_BACKUP in sys.databases.) My next question would be why would this be If is a mananged service, Surely the log should not grow and should be backed up and truncated? ? We ran out of space because of the issue. Our SLA's don't really require the logs to be backed up, so simple is fine. I don't want the log to keep growing though until we run out of space!
April 27, 2018 at 4:24 pm
sharky - Thursday, April 26, 2018 6:25 AMThom, Erikur, Thanks for the help.. Understand a bit more how AWS works.. thanks.. The problem was that the log was growing to a few gig (log_Reuse_Wait_description = LOG_BACKUP in sys.databases.) My next question would be why would this be If is a mananged service, Surely the log should not grow and should be backed up and truncated? ? We ran out of space because of the issue. Our SLA's don't really require the logs to be backed up, so simple is fine. I don't want the log to keep growing though until we run out of space!
Did you find a resolution for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2018 at 6:50 am
Thom A - Thursday, April 26, 2018 5:34 AMPerhaps this might help: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.DatabaseRecovery.htmlSpecifically:
Changing a database’s recovery model to “Simple†while backup retention is enabled will result in Amazon RDS setting the recovery model to “Full†within five minutes of the setting change. This will also result in Amazon RDS taking a snapshot of the DB instance.
+1
...
April 28, 2018 at 11:56 am
Jeff.. What I did in the end was to set the database to simple and immediately did a DBCC shrinkfile of the log files before it reverted back to full. The problem started when the client restored the previous non RDS databases (all in Full recovery without ever taking log backups) on a new RDS instance. The RDS backup was not able to truncate the log and it kept growing. At first I was confused why AWS would allowed the database to be set to simple in the first place, but as it provided a nice workaround ( perhaps by design although I could not found this documented??) it saved me a lot of work and don't complain to much..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply