March 18, 2011 at 10:00 am
homebrew01 (3/18/2011)
If you're consulting, and there's a disaster, maybe you'll get more billable hours to help fix it. 😀Maybe someone in the business end needs to know they have 23 hour data loss potential (hardware, application error, human error) and are they "OK" with that ?
I suppose the Admin doesn't hit "SAVE" periodically when working on a spreadsheet for 5 hours ?? He just waits until the end ?
No he's actually really smart, and he never had problems with "his" stuff. Hence my "what if" has very little weight.
March 18, 2011 at 10:07 am
Ninja's_RGR'us (3/18/2011)
No he's actually really smart, and he never had problems with "his" stuff. Hence my "what if" has very little weight.
Sound as if a little "accident" with his power cable might be in order. 😉
March 18, 2011 at 10:10 am
Ian Scarlett (3/18/2011)
Ninja's_RGR'us (3/18/2011)
No he's actually really smart, and he never had problems with "his" stuff. Hence my "what if" has very little weight.Sound as if a little "accident" with his power cable might be in order. 😉
Let's stick with diplomacy at this point in time... however tempting that may be ;-).
The db doesn't take in a lot of data, but it's still 8 hours times a lot of employees.
March 18, 2011 at 10:34 am
Ninja's_RGR'us (3/18/2011)
No he's actually really smart, and he never had problems with "his" stuff. Hence my "what if" has very little weight.
What If:
Monday 1 am Full Backup
Monday 10 am Log backup
Monday 1 pm Very Important Updates to Table_A and Table_B
Monday 2 pm DROP TABLE_A (oops)
Monday 3 pm DELETE TABLE_B where ACCT_TYPE = 'Z' (oops)
Tuesday 1 am Full Backup
Tuesday 9 am "Um ... I dropped table_A yesterday afternoon, then accidentally deleted some records from Table_B .... can you get them back for me ?"
His reply is ??
March 18, 2011 at 10:42 am
Actually we do have 3 days of full and log backups and in this case the live db and tlog.
So while it would be a pita I guess it could be done. I say guess because I still haven't tested if the double dip backups screw up the point in time restore.
March 18, 2011 at 10:43 am
His reply will be known next week because we're both heading out for the week-end now.
Thanks again and keep 'em coming!
March 18, 2011 at 10:46 am
Basically, if you want to do log backups at all, you'll want to do them fairly often!
Doing so only once a day is both a lot riskier and far worse for performance than doing so every hour, for example.
You see, first there is the point that has already been covered: when something bad happens, you might not get to make your log backup at all that day and be forced all the way back to your last full backup.
Second, the longer you wait for your log backup, the larger the log files have to be in order to store info about everything that happens to your databases in between log backups - and the larger and heavier the backup job itself. Also, in a large and busy production environment, constantly having to update a big, fat log file can actually slow down your entire database operation. ( ! )
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 18, 2011 at 10:46 am
You said you're only doing a t-log backup once a day. In my scenario, you would need a t-log backup sometime after 3 pm, and before the next full backup. But the one t-log backup for the day already happened at 10 am.
March 18, 2011 at 10:51 am
homebrew01 (3/18/2011)
In my scenario, you would need a t-log backup sometime after 3 pm, and before the next full backup.
Assuming all the backups are intact and restorable, why would you need a log backup from before the full?
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
March 18, 2011 at 10:53 am
homebrew01 (3/18/2011)
You said you're only doing a t-log backup once a day. In my scenario, you would need a t-log backup sometime after 3 pm, and before the next full backup. But the one t-log backup for the day already happened at 10 am.
Actually, there is no requirement for it to be "before the next full backup", since a full backup never breaks a log chain. If your database is still online, you can just make the log backup when you get the restore request at 9 the next morning and roll it on top of the log backup from yesterday, ignoring the full backup that's run in the meantime.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 18, 2011 at 11:05 am
The reason you might need logs from before the full is if your full is broken.
Backup 1 - Full backup
Backup 2 - Log backup
Backup 3 - Full backup
Backup 4 - Log backup
Failure
If backup 3 is unreadable, and that could happen if it's on the server and the drives are gone, then you are going back to backup 1, which means you might want backup 2 (log backup).
I had a policy to never remove log backups before removing the full to which that follow. So I would not delete backup 2 until backup 1 was deleted. That might not be until I had another full backup made.
In terms of why not backup the log during the day, what's the harm? The meta data is small and if your once a day log backup is 24MB, then your hour log backups would be around 1MB. roughly the same size in aggregate.
IF there are performance reasons, I can possibly understand, but in that case you want to make sure that log backup is off the drives and away from the data and log files in case you lose SAN drives. Thinking the SAN is bulletproof is asinine.
March 18, 2011 at 11:10 am
GilaMonster (3/18/2011)
homebrew01 (3/18/2011)
In my scenario, you would need a t-log backup sometime after 3 pm, and before the next full backup.Assuming all the backups are intact and restorable, why would you need a log backup from before the full?
Because I've been making mistakes all week, so I didn't want today to be an exception.
Edit: I must be "simple" minded today.
March 18, 2011 at 11:37 am
Steve Jones - SSC Editor (3/18/2011)
The reason you might need logs from before the full is if your full is broken.
Yeah, but you don't need an extra right before the full backup (assuming all backups are intact)
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
March 18, 2011 at 2:10 pm
Assuming the question is directed to me.
I do the daily tlog daily because we are on full rather than simple (didn't want to make thing even worse) and I want to avoid the "log is full" issue which they had about 9 months ago.
Performance is not an issue at all. We have well over 8 hours of maintenance where we can go slower, if not stopped, and the whole backup sequense including checkdb run in less than 30 minutes. Index maintenance is around 15 minutes but I have a lot of wait time in ther in case I ever need to rerun it in prod hours.
We even ran those during the day a few times and the users barely noticed.
The issue we used to have was space on the san. A new 100 TB san was purchased early this year, and yet <almost> no extra space is being allocated to the sql systems.
March 18, 2011 at 2:13 pm
Vegard Hagen (3/18/2011)
Basically, if you want to do log backups at all, you'll want to do them fairly often!Doing so only once a day is both a lot riskier and far worse for performance than doing so every hour, for example.
You see, first there is the point that has already been covered: when something bad happens, you might not get to make your log backup at all that day and be forced all the way back to your last full backup.
Second, the longer you wait for your log backup, the larger the log files have to be in order to store info about everything that happens to your databases in between log backups - and the larger and heavier the backup job itself. Also, in a large and busy production environment, constantly having to update a big, fat log file can actually slow down your entire database operation. ( ! )
Our actual tlog is around 7 GB per day. But I reckon that 80% of that comes from index maint at night. That takes less than 5 minutes to run. So perf is not really the reason behind the decision here.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply