February 3, 2011 at 3:00 pm
I have a database that is 70GB and a log file that is 45GB. I do full backups everyday at 3am via a maintenance plan. Do I need the log or should i truncate the log after the full backup? I am not completely clear on what the log file does.
Thanks,
Mike
February 3, 2011 at 3:09 pm
Mike, are there more senior DBAs on staff, or did you get volunteered to the position because noone else will do it? The reason I ask is to know what resources are available to you. You're in deep.
Keeping this simple: The log file is a list of every activity that has occurred in the database that changed something. Be it data, table schema, or options. The reason for this is two fold. One, point in time restores when bad data gets in, and two: In case of crash it can make sure your data is still whole. Imagine running, say, a 5 minute update statement on a large table. 2.5 minutes in, the system crashes. The log file lets you return to what the data looked like before you started. In this way, the data stays in a consistent state.
Now, to know what to do with your log file, you need to look at what your recovery method is. It'll say: Simple, Bulk-Logged, or Full. In simple, leave it be, it re-uses its space and you just need to take regular backups.
In bulk-logged, or full, you need to take transaction log backups to clear up space at the beginning. However, you need to determine if you need to do this. How much data can your company lose? What log backups do is let you take 'mini-backups', just the changes since the last time you did a mini-backup.
To understand what you want to do with the log more fully, research "Point in Time Restore" on google a bit. You might also look into differential backup while you're there.
The other thing you want to look into is 'recovery mode'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:53 pm
This might be worth a read - Managing Transaction Logs[/url]
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 3, 2011 at 4:22 pm
Craig Farrell (2/3/2011)
Mike, are there more senior DBAs on staff, or did you get volunteered to the position because noone else will do it? The reason I ask is to know what resources are available to you. You're in deep.Keeping this simple: The log file is a list of every activity that has occurred in the database that changed something. Be it data, table schema, or options. The reason for this is two fold. One, point in time restores when bad data gets in, and two: In case of crash it can make sure your data is still whole. Imagine running, say, a 5 minute update statement on a large table. 2.5 minutes in, the system crashes. The log file lets you return to what the data looked like before you started. In this way, the data stays in a consistent state.
Now, to know what to do with your log file, you need to look at what your recovery method is. It'll say: Simple, Bulk-Logged, or Full. In simple, leave it be, it re-uses its space and you just need to take regular backups.
In bulk-logged, or full, you need to take transaction log backups to clear up space at the beginning. However, you need to determine if you need to do this. How much data can your company lose? What log backups do is let you take 'mini-backups', just the changes since the last time you did a mini-backup.
To understand what you want to do with the log more fully, research "Point in Time Restore" on google a bit. You might also look into differential backup while you're there.
The other thing you want to look into is 'recovery mode'.
I'm it. We are using full recovery mode. We do a full backup everyday at 3am and transactional backups from 6am - 8pm everyday.
February 3, 2011 at 4:25 pm
mike 57299 (2/3/2011)
I'm it. We are using full recovery mode. We do a full backup everyday at 3am and transactional backups from 6am - 8pm everyday.
That seems a reasonable setup. It sounds like you do some very heavy data import/exports overnight or some heavy calculations and the like between 8PM and 6AM. The full db backup doesn't backup the TLog at the same time, they're unique(ish) entities. There are a number of reasons for this and it makes life easier in case of a corrupted backup.
I wouldn't shrink the log file in this case unless you can confirm that most of that space was used during the initial build and has never been required again.
However, under no circumstances should you ever truncate this log unless you're prepared to immediately take a full backup. You might shrink it, but never truncate it, if you need full recovery options.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 12:09 pm
Ok... I am still a little confused. Would I be safe in doing the following:
1) Checkpoint
2) Backup Log File
3) DBCC Shrinkfile ( log file), 0, Truncateonly
4) Full Backup
5) delete any log file backups older than 1 week.
Questions:
1) Would this allow me to completely restore the data if I had a crash?
2) What about the system database(s)? Do I need to do a separate backup?
Thank you all!
February 4, 2011 at 12:22 pm
mike 57299 (2/4/2011)
Ok... I am still a little confused. Would I be safe in doing the following:1) Checkpoint
2) Backup Log File
3) DBCC Shrinkfile ( log file), 0, Truncateonly4) Full Backup
5) delete any log file backups older than 1 week.
Don't truncate your logfile except in emergency situations. If you must, shrink it... but don't truncate it. It's like Truncate table and it blows your recovery out of the water. Also, you want to keep your logfile to a reasonable size when you shrink it, and the backup should clear up most of the 'used' space in it. I would start with a nice 8000M (not gig) log file for good VLFs and expand from there as needed.
First what's a VLF? see here: http://msdn.microsoft.com/en-us/library/ms179355.aspx
Why that number? see here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Warning, that can get a bit overwhelming.
1) Would this allow me to completely restore the data if I had a crash?
Only from the last backup, you'd lose the chain from the prior one.
2) What about the system database(s)? Do I need to do a separate backup?
Depends on your maintenance plan, but yes, all databases must be backed up to unique files.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 12:23 pm
Leave point 3 out. It is a bad idea to regularly shrink the log. It should only be shrunk after some unusual (once-off) operation has caused it to grow far beyond what it needs to be.
You need to be backing the log up far more than once a day. Every 30 min-1 hour to start with
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 4, 2011 at 12:24 pm
Craig Farrell (2/4/2011)
mike 57299 (2/4/2011)
Ok... I am still a little confused. Would I be safe in doing the following:1) Checkpoint
2) Backup Log File
3) DBCC Shrinkfile ( log file), 0, Truncateonly4) Full Backup
5) delete any log file backups older than 1 week.
Don't truncate your logfile except in emergency situations. If you must, shrink it... but don't truncate it.
Shrink file does not ever truncate the log, no matter what options are used. The truncate_only option on shrink file is used when shrinking data files, not log. It's ignored when shrinking the log file.
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 4, 2011 at 12:31 pm
GilaMonster (2/4/2011)
Craig Farrell (2/4/2011)
mike 57299 (2/4/2011)
Ok... I am still a little confused. Would I be safe in doing the following:1) Checkpoint
2) Backup Log File
3) DBCC Shrinkfile ( log file), 0, Truncateonly4) Full Backup
5) delete any log file backups older than 1 week.
Don't truncate your logfile except in emergency situations. If you must, shrink it... but don't truncate it.
Shrink file does not ever truncate the log, no matter what options are used. The truncate_only option on shrink file is used when shrinking data files, not log. It's ignored when shrinking the log file.
*facepalm* Thanks, I'd forgotten that. So used to the old Backup with truncate to get out of space failures on badly maintained systems that my brain jumped circuits.
Appreciated as always.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 12:36 pm
Craig Farrell (2/4/2011)
*facepalm* Thanks, I'd forgotten that. So used to the old Backup with truncate to get out of space failures on badly maintained systems that my brain jumped circuits.
It's a current pet-peeve of mine. That said, there are far too many truncates in SQL Server (just like there are too many snapshots)
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply