February 13, 2017 at 6:59 am
Hi, DBA's out there in the big wide world just need some insight
I am a SQL Developer (DBA light) and hand holding a prod system.
Files are delivered by ETL via ssis and then a process then kicks off by a sql job.
Prior to process kicking off the recovery mode is set to SIMPLE from FULL
and at the end, the mode is set back to FULL after the process finishes and goes off to do
other stuff in FULL recovery mode.
This process whilst in SIMPLE mode creates at least 150GB of transactions in the log.
the DB and the log is backed up daily and the recovery mode is set to SIMPLE momentarily while the log is shrunk
using DBCC shrinkfile and then flipped back to FULL.
My simpleton questions are
1) What does the flip flopping achieve ?
2) Are the 150 GB worth of transaction inert/dead in the log backup.
i.e. if you had to do a restore from the last db backup and apply the log
would these 150 GB be applied/not applied and only the stuff applied whilst in FULL mode
3) Once the SIMPLE is applied haven't we in fact broken any chain and blown the whole aspect of db recovery
Am I missing a trick here...
February 13, 2017 at 7:12 am
robinrai3 - Monday, February 13, 2017 6:59 AM1) What does the flip flopping achieve ?
It breaks the log chain, leaves you unable to recover past that point. Otherwise probably nothing that setting to Bulk_Logged wouldn't do just as well
2) Are the 150 GB worth of transaction inert/dead in the log backup.
i.e. if you had to do a restore from the last db backup and apply the log
would these 150 GB be applied/not applied and only the stuff applied whilst in FULL mode
Probably the 150GB is just the space needed for the operations and the log is empty afterwards. You won't be able to restore the last DB backup and apply the log backups to get to the point after that process, because the switch to simple broke the log chain.
3) Once the SIMPLE is applied haven't we in fact broken any chain and blown the whole aspect of db recovery
You have indeed. You'll be able to restore to the last log backup taken before that process started, but every log backup afterwards will fail until a new full/diff backup is taken.
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
February 13, 2017 at 7:42 am
HI Gail,
Thanks for prompt reply...you've clarified a lot
So to put a bit more belt and braces on this
1) Prior to flipping to SIMPLE mode, if a transaction log back up was taken, then if need be, the db can be restored by applying the last db backup and this latest log.
2) I agree the 150GB is used for the processing, so really before the log is flipped back to FULL we should do a db backup again since the chain has been broken and then it starts again...do you concur on this ?
February 13, 2017 at 8:13 am
robinrai3 - Monday, February 13, 2017 7:42 AM1) Prior to flipping to SIMPLE mode, if a transaction log back up was taken, then if need be, the db can be restored by applying the last db backup and this latest log.
The last log backup taken and any others too that have occurred since the last full backup
robinrai3 - Monday, February 13, 2017 7:42 AM
2) I agree the 150GB is used for the processing, so really before the log is flipped back to FULL we should do a db backup again since the chain has been broken and then it starts again...do you concur on this ?
No, after the flip back to Full, you then take either a full backup or a differential backup, they both restart the log chain.
It's a common misconception that in simple mode sql server carries out less logging. Some actions are minimally logged its true, but the log can still grow wildly and runaway even in simple mode with truncate on checkpoint active.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2017 at 8:30 am
Thanks Perry ..got it ... you've both (Gail also) filled in the pieces...looks as if both of these steps are missing from this current process. You deserve a doughnut each...cheers
February 13, 2017 at 8:41 am
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2017 at 10:59 am
robinrai3 - Monday, February 13, 2017 7:42 AM2) I agree the 150GB is used for the processing, so really before the log is flipped back to FULL we should do a db backup again since the chain has been broken and then it starts again...do you concur on this ?
No. Don't switch at all. If you're looking for the minimal logging, then switch to bulk-logged and back to full, doesn't break the log chain. (and you'd need to take the full/diff after switching back to full, not before)
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
February 14, 2017 at 6:26 am
Bulk-logged still has a couple of problems:
1. Point in time recovery will not work for any time the DB was in bulk-logged mode.
2. While bulk-logged recovery logs less than full recovery it can still amount to quite a bit.
If the most of the data you are loading is not needed, or will be summarized, another approach is to:
1. Create another DB with simple recovery.
2. Place the load tables in the new DB.
3. Create Synonyms in the main DB to the load tables in the new DB.
This approach means:
1. the main DB can be kept in FULL recovery all the time with minimal impact on the logs.
2. you have complete point in time recovery for the data you are really interested in.
3. the log backups for log shipping etc are kept at a reasonable size.
February 14, 2017 at 7:10 am
Ken McKelvey - Tuesday, February 14, 2017 6:26 AMBulk-logged still has a couple of problems:
1. Point in time recovery will not work for any time the DB was in bulk-logged mode.
2. While bulk-logged recovery logs less than full recovery it can still amount to quite a bit.
On 1: Point in time recovery does not work within any log interval that has minimally logged operations in it
On 2: But it logs the same amount as Simple recovery, the only difference is in what marks the log as reusable (checkpoint in simple, log backup in bulk-logged)
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
February 14, 2017 at 7:25 am
GilaMonster - Tuesday, February 14, 2017 7:10 AMKen McKelvey - Tuesday, February 14, 2017 6:26 AMBulk-logged still has a couple of problems:
1. Point in time recovery will not work for any time the DB was in bulk-logged mode.
2. While bulk-logged recovery logs less than full recovery it can still amount to quite a bit.On 1: Point in time recovery does not work within any log interval that has minimally logged operations in it
On 2: But it logs the same amount as Simple recovery, the only difference is in what marks the log as reusable (checkpoint in simple, log backup in bulk-logged)
On 1: Sorry, you are correct. Presumably the whole point of putting a DB into bulk-logged recovery is to run minimally logged operations.
On 2: True but even if the log is being backed up every 5 minutes this can still result in a significantly larger log file than simple recovery.
February 14, 2017 at 10:45 am
Ken McKelvey - Tuesday, February 14, 2017 7:25 AMTrue but even if the log is being backed up every 5 minutes this can still result in a significantly larger log file than simple recovery.
Au contrair, it's still possible to have a runaway log in simple mode, i've seen it many times before over the years.
Occurs under set circumstances, IIRC truncate on checkpoint only kicks in when the log is 70% full along with certain other criteria, a long running transaction can easily cause a log to fill.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply