| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2011-01-11 : 11:34:00
|
| my initial tlog backup after full backup still seems to include everything that occured during the evening. how can i truncate the transaction log as part of the backup so that the first tlog backup of the day isnt several gb in size. database is in full mode |
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-01-11 : 11:42:12
|
| Have you looked at this article?http://support.microsoft.com/kb/873235I believe it may give some strategies you can consider. See this one as well:http://msdn.microsoft.com/en-us/library/aa174538(v=sql.80).aspx===http://www.ElementalSQL.com/ |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-01-11 : 11:47:45
|
quote:
Originally posted by RobertKaucher
Have you looked at this article?http://support.microsoft.com/kb/873235I believe it may give some strategies you can consider. See this one as well:http://msdn.microsoft.com/en-us/library/aa174538(v=sql.80).aspx===http://www.ElementalSQL.com/
Thanks much Robert, I was just under the impression that doing a full backup of the database would truncate the log??? Doesnt seem to be the case, its just goofy to me that after a full backup, the tlog would include all the activity from the evening even though i dont need that activity since I have the full??? |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-01-11 : 11:51:30
|
| I suppose, since my next tlog backup is after all of that happened, its going to capture it now that I'm thinking about it. So i should probably just flip the db to simple during the load process, and flip back to full after. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-11 : 13:22:05
|
| "I was just under the impression that doing a full backup of the database would truncate the log??? Doesnt seem to be the case, its just goofy to me that after a full backup, the tlog would include all the activity from the evening even though i dont need that activity since I have the full???"Doing a Full or Differential backup is completely independent of the Log. In simplistic terms:Full / Differential backup "pages" from the data file.Backing up the Log copies pages from the Log file, and marks them as "available" so new entries can overwrite those pages.You can restore a full backup and all the Log backups since ...You can restore the full backup from a month / a year ago and all the Log backups since ...This is crucial when/if the database becomes corrupted, because you can go back until you find a "clean" full backup, and then restore all log backups since. Because the log files and backups are independent (and ideally on a separate drive and controller) there is a very good chance that the Log Backups will not be corrupted when the database is (and if the database is corrupted then the backups taken later will also be corrupted as they are just copies of the pages in the file)If taking a full backup was in some way associated with the logging system / backups then this would not be possible. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-11 : 13:25:51
|
| "flip the db to simple during the load process, and flip back to full after"That will break the backup chain, so you will not be able to make any log backups until you take another FULL backup, and you will not be able to restore "across" that break (e.g. if it turns out that that full backup was corrupted) |
|
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-01-11 : 13:48:16
|
quote:
Originally posted by Kristen
"flip the db to simple during the load process, and flip back to full after"That will break the backup chain, so you will not be able to make any log backups until you take another FULL backup, and you will not be able to restore "across" that break (e.g. if it turns out that that full backup was corrupted)
That's a very important issue.duhaas, why not just perform a t-log backup as well? This would give you the added benefit of being able to perform a point in time restore if required.===http://www.ElementalSQL.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-11 : 13:52:56
|
| GOing back to the original question:"how can i truncate the transaction log as part of the backup so that the first tlog backup of the day isnt several gb in size"my answer would be "Make more frequent Log backups" [during the night] |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-01-11 : 14:05:36
|
| Thanks everyone for the great input, problem with all the activity at night is i dont need it, something goes wrong, i go back to the full that was taken @ start of night, and then we take another @ end of night. we take tlog backups during the day between 7/7, but from 7 on nothing, hence leading to the large log i'm guessing, does that make sense? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-01-11 : 15:53:15
|
| It makes sense for why that log backup is so large. What doesn't make sense is the schedule - I would schedule the transaction log backups throughout the night.I wouldn't necessarily backup the databases twice - and I would consider using a smart re-indexer to only rebuild those indexes that need to be rebuilt.Jeff |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-01-11 : 16:37:23
|
| the reason the schedule is like that is i want a copy of database prior to nightly load, and a copy after the load, should something fail in the load, i want to revert back to the prior load version. i would never be in situation where i need any point in time during the night. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-11 : 16:45:47
|
| And what if your Full Backup that you try to recover from is faulty?Either you don't need point-in-time recovery, and you are prepared to lose all changes since the last full backup (in which case switch to SIMPLE recovery model), or you do.I think the Log data that are generated by Index Rebuilds is a PITA because of the amount of data that has to be backed up. We generate nearly 3GB of backups each night just during a short period of Index Rebuilds, and that is with systems built to be smart enough to only rebuild indexes that are badly fragmented. But I don't fiddle with the Logs because of it, I live with it. (In fact we increase Log backup frequency from 15 minutes to 2 minutes during index rebuild to stop the LDF file growing exceptionally compared to other times of the day) |
|
|
|
|
transaction log size after full backup