August 22, 2016 at 3:11 pm
I understand the benefits of using FULL and or BULK Logged recovery models in as far as backup/restore are concerned but if the log file for a DB is not being backjed up then is there any reason for setting a DB recovery Model to BULK LOGGED or FULL versus using SIMPLE?
For some reason the DB that is used by a software/service we use is regularly changed from FULL to BULK LOGGED and back to FULL again throughout the day. I can see it in SQL Servers log files and I can see it happening if I turn on Profiler and capture the activity. I found a Stored procedure inside the DB that consists SET RECOVERY commands and its called numerous times throughout the day. Before we contact the vendor about this I'd like to know if there is a SQL SERVER reason/benefit to doing this seperate from whatever reason the software vendor provides. We have never backed up the log file for this DB so I'm puzzled as to why this thing is constantly changing the DB's recovery model.
Kindest Regards,
Just say No to Facebook!August 22, 2016 at 3:33 pm
MS explains it in this article:
https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx
You'd want to check the link to Operations That Can Be Minimally logged.
Sue
August 23, 2016 at 4:20 am
You've got a DB in full or bulk-logged recovery model and you've never backed up the log? Might want to fix that before the log fills the drive (log space cannot be reused until backed up if the DB is in full/bulk-logged)
The reason for swithing from full to bulk-logged is to get minimal logging for data loads without breaking the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 1:46 pm
We don't want the DB to be set to FULL or BULK LOGGED; the application keeps reverting the recovery model back to FULL and then Bulk Logged anytime we try to change it to SIMPLE. We don't need to do backups of the log because the entire server is on a DR systems so that if anything happens the entire server is restored.
I have not yet heard back from the vendor but I'm guessing they built the system assuming that the DB would always be set to FULL and so they built it to swap the RECOVERY MODEL to FULL anytime its not explicitly supposed to be set to BULK LOGGED.
So if you want a DB to be set to SIMPLE is there still a benefit to switching it to BULK LOGGED for minimal logging?
Thanks
Kindest Regards,
Just say No to Facebook!August 23, 2016 at 2:07 pm
No. Anything that's minimally logged in bulk-logged is minimally logged in simple.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 2:08 pm
YSLGuru (8/23/2016)
We don't need to do backups of the log because the entire server is on a DR systems so that if anything happens the entire server is restored.
And I'm sure you have something in place to fix accidental deletes/data changes...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply