| Author |
Topic |
|
nicole
Yak Posting Veteran
97 Posts |
 Posted - 2006-01-18 : 05:26:14
|
I scheduled both the full database backup and transaction log backup to run daily at 6:00am in Database Maintenance Plan. After reading several articles, knowing that transaction log keeps the transactions performed against the database since the last backup. So, I wonder if it is meaningless to run the transaction log back up at the same time as database backup  . Any of you can give me adivse about when to backup the transaction log?Also, the log file size is getting bigger and bigger. Now there is 9GB data but the log file size is 6GB  . I thought the log will automatically truncated itself after performing full backup, but it seems not. What can I do to reduce the log file size  |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-18 : 05:32:22
|
| Do you need point in time recovery? If not, switch your transaction log recovery model to simple and stop the tran log backups.Otherwise, it would make more sense to take the logs at a different time than the full backup, because otherwise you can only restore to the same time as if you didn't take the tran log backup at all. Also the two processes may be conflicting in some way and creating your ever growing log.It should release log space after the backup but you may have a stuck transaction.-------Moo. :) |
 |
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-01-18 : 08:23:13
|
| If the transaction log is set to be backed up per hour, for example, there will have many log backup file on the server, is it correct? When doing tape backup, all log backup files have to copy, right? |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-01-18 : 08:24:50
|
| log space <> physical file size, right? what to do if I want to reduce the physical file size after each backup in order to free the disk space? Have to do it manually? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-18 : 08:27:39
|
quote:
If the transaction log is set to be backed up per hour, for example, there will have many log backup file on the server, is it correct? When doing tape backup, all log backup files have to copy, right?
Yes you would have lots of backup files.Yes, you would need to copy all those backups to tape to maintain a log sequence enabling point in time recovery. quote:
log space <> physical file size, right? what to do if I want to reduce the physical file size after each backup in order to free the disk space? Have to do it manually?
Yes, you wont automatically get the physical space back. However, manual shrinking is fairly pointless as the file will grow again next time the space is needed which has a high cost in terms of I/O performance. If the log routinely needs that much space, then it needs that much space.-------Moo. :) |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-01-18 : 21:02:52
|
I got it now  Thanks for your explanation  In order to make the transaction log backup useful, I think I should change the schedule to run it during lunch hour  And..... increase the HD disk space  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-19 : 02:21:28
|
| "In order to make the transaction log backup useful, I think I should change the schedule to run it during lunch hour"PMFBIIf you only back it up once a day then I think you should consider if you need it at all.Do you want to be able to restore to point-in-time?If not set the database Recovery Model to SIMPLE and just do your full backups.If you do want point-in-time then I suggest you backup more often - at least hourly, maybe every 10 minutes or so.Your Transaction Log (i.e. LDF) file will grow to the largest size it needs to to store all the transactions that have not-yet-been-backed-up.If you backup the Transaction Log often the LDF file will be smaller - because it is getting clearer more often - so it only needs to store an hours work, say, instead of 24 hours.Yes there will be more backup files, but the total size of them will be the same as the 24-hour single backup file.And potentially the LDF file will be smaller.Once you've sorted this out (one way or the other!) I suggest you do a one-time shrink of the LDF file and then let it grow back to its working size. Truncate it again only if it grows exceptionally - e.g. you do some massive unusual delete.Kristen |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-01-22 : 20:49:21
|
It seems I should back up the the log file frequently  |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-01-22 : 20:51:59
|
| I'm not sure, other than massive delete, is there any reason why the database backup file size to be reduced I found that it was 9.1 GB on Saturday, but was 8.8GB on Sunday |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-23 : 02:01:32
|
| "It seems I should back up the the log file frequently"This might help: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=What+type+of+backup+should+I+use"is there any reason why the database backup file size to be reduced "Only thing I can think of is that the Backup file contains the database any transactions that occur whilst the backup is being made.The backup file only contains the parts of the original database file that are in use so, as you say, deletions today will mean a smaller backup file tomorrow [but the database file itself won't get smaller unless you explicitly shrink it (which is generally a bad idea]).I expect its also possible that a Reindex or Defrag could reorganise the data "tighter" such that less backup file space was required.So in the absence of deletions maybe there were some large transaction on Saturday whilst the backup was being made and none on Sunday?Kristen |
|
|
|
|
Transaction log backup