Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-04-21 : 10:52:25
|
| I've read a number of things regarding the SQL Server Transaction Log File relating to it growing out of control. I don't have membership to www.sqlservercentral.com so I could not read Gila's article. A few things I am still scrathing my head over. Our Hard Drive recently went full and the one log files was 200g, even though we were doing regular Database Backups.1. If we are in Full Recovery Mode, the only 2 ways to keep the size of the log file under control is: 1) Set Log file to Restricted Growth and 2) Backup the Log File. Is this correct?2. If we are in Full Recovery Mode, a database backup will not reduce the size of the logfile? If not, why not? If the Database is backed up, wouldn't that mean we don't need as much of the Transaction Log File?3. Is there any use to shrinking the Log File? Or is that not required if we back up the Log file? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 11:27:36
|
A database backup will not clear the log file, only a log backup can do that.Reason being:Example: you make a full backup every night at midnight, and a log backup every 15 minutes.You want to restore to 9am on Tuesday. You restore FULL backup from Monday night, then every TLog backup after that unill the 9am one.However, you then discover that Monday night's backup is damaged / on tape off site / whatever, so instead you restore from Sunday nights backup and all TLog backups for both Sunday and Monday (up to 9am TLog backup on Monday)So Database backup must not clear the Tlog file for this to be possible.If this was not the case then if someone took a quick Full backup (before doing some action that they might want to recover from) it would be critical that that backup was kept in case it needed to be used as part of a restore.Back to my example, you would be restoring from Monday night database backup and all TLog backups since, and then discover that someone had taken a Full backup at 8am on Monday, and you would then have to find, and restore, that backup to get to 9AM position.Having the Log and Database backups completely independant of each other solves that.So ... take a full Database backup as often as you need to - e.g. once a day.Take a Log backup every 15 minutes. Then your TLog file should only need to be large enough to hold the busiest 15 minutes during the day (which will probably be Index Maintenance  )Once you have that sorted out it is worth doing a one-time shrink to get the Tlog file down to a reasonable size. Don't repeatedly shrink it as that will fragment the file and adversely impact on performance. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-21 : 11:54:51
|
| Why don't you sign up for membership at SSC? It's free, and you can decline the daily newsletter (which is the only mail you'll get). I could be mistaken, but I think you can read the articles without membership, you just can't post.--Gail ShawSQL Server MVP |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-04-21 : 12:09:53
|
quote:
Originally posted by Kristen
A database backup will not clear the log file, only a log backup can do that.Reason being:Example: you make a full backup every night at midnight, and a log backup every 15 minutes....
Wow, I hadn't considered any of that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 12:54:44
|
| :ThumbsUp: |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-04-22 : 10:44:51
|
| So now a new question occurred to me:Say the log backups are every 1/2 hour on the 1/2 hour. And the most recent Database backup is at 8:15 am. And say we want to restore it plus all transactions since this backup.If we apply all logs since 8:30 am, we miss transactions between 8:15 and 8:30am? But can we apply all logs since 8:00am or would that duplicate transactions from 8:00 to 8:15am? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-22 : 11:00:36
|
| Restore the full, then all log backups starting with the 8:30 one. SQL's not stupid, it will roll the log forward based on the LSNs (log sequence numbers), hence only the transactions not included in the full will be restored.If you try skipping the 8:30 log backup, you'll get an error saying that the LSNs don't match.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Log Backups/ Log full