March 8, 2019 at 1:01 pm
Hello room,
I have 25 production databases that running on Full recovery mode.
Someone changed from Full to Simple recovery mode and he believed the full recovery mode and log backups will use a lot of I/O.
He also believed that the log backup will contribute to a high I/O on SQL Server.
I'm not hundred percent sure but I did not agree with him.
I try to clarify in simple term the different Full and Simple recovery mode.
Full recovery mode --> allows the database to be recovery point of time.
Simple recovery mode --> There are no log backup and possible data lost in case of database corruptions or failing.
And, I recommended to setup maintenance jobs like
-- Full User database backup ---> Every Sunday at 1am
-- Differential User database backup --> Monday - Saturday at 1am
--Log backup --> Daily every 1 hour
Would someone clarify on high I/O usage on T-Log backup and database Full Recovery mode?
It's that true?
Thank you in advance.
-Edwin
March 8, 2019 at 1:45 pm
Ed7 - Friday, March 8, 2019 1:01 PMHello room,I have 25 production databases that running on Full recovery mode.
Someone changed from Full to Simple recovery mode and he believed the full recovery mode and log backups will use a lot of I/O.
He also believed that the log backup will contribute to a high I/O on SQL Server.I'm not hundred percent sure but I did not agree with him.
I try to clarify in simple term the different Full and Simple recovery mode.
Full recovery mode --> allows the database to be recovery point of time.
Simple recovery mode --> There are no log backup and possible data lost in case of database corruptions or failing.And, I recommended to setup maintenance jobs like
-- Full User database backup ---> Every Sunday at 1am
-- Differential User database backup --> Monday - Saturday at 1am
--Log backup --> Daily every 1 hourWould someone clarify on high I/O usage on T-Log backup and database Full Recovery mode?
It's that true?Thank you in advance.
-Edwin
Choosing a recovery model has nothing to do with IO. All transactions in whatever recovery mode are written to the log. I guess he doesn't get that. Doing any backup does incur some IO but the recovery model is based upon the business requirements and needs for recovery and not some other unintelligent dribble.
You could stop backing up anything everywhere in the environment and you have reduce your IO. You have likely reduced your chances of the business being an ongoing concern as well.
What some people will come up with...keep pushing, you're on the right track.
Sue
March 8, 2019 at 2:51 pm
There are (very few) things that would write to the transaction log in Minimal Logging mode if the database is in SIMPLE recovery compared to FULL recovery mode, but as Sue mentions what is more important is meeting the business needs. If these are production environment databases that the business is running important systems on, then you absolutely do want FULL recovery mode. To elaborate on meeting the business requirements, the two main concepts are:
RPO: (recovery point obective) How much data is the business willing to loose if there is a database failure?
RTO: (recovery time objective) How long will it take to bring a database back after a failure?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply