| Author |
Topic |
|
dholland
Starting Member
11 Posts |
 Posted - 2008-03-28 : 14:37:39
|
| I am currently running log shipping... primary server and secondary server in standby/read only so that we can run reports/etc off of it. My question is... Does log shipping clean up the transaction logs for the database? I know that it cleans up the backups it creates... I have it currently set to 72 hours... however what about the database tran log? (.ldf).. I know that if I do a full backup and clear that file out it's going to break my log shipping right? So how do I keep the .ldf manageable... or does log shipping do that for me already?Thanks,Dave |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-28 : 14:55:27
|
| First of all don't post Log shipping question in this forum.Yes if you backuping transaction log every 10-15 minutes .Transaction log will be managed and delete old transaction log backup so that you don't fill up disk space. |
 |
|
|
dholland
Starting Member
11 Posts |
Posted - 2008-03-28 : 16:04:32
|
| As much as I appreciate a reply - if you want to tell me not to post here why don't you tell me where I should have posted... if you notice I'm a starting member so I apologize if I'm not as familiar with the site. I'd like to go to the right place and repost to get more information. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-28 : 17:01:19
|
| If you do a full backup and delete it, it will not break log shipping. Log shipping only uses the very first full backup file, after that it only uses the transaction log backups. The ldf file is the transaction log and is different from transaction log backups. So I'm confused as to your questions.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dholland
Starting Member
11 Posts |
Posted - 2008-03-28 : 17:59:42
|
| Let me elaborate some...I have log shipping set up, it's working.. the logs are being shipped and the job is cleaning up the logs after 72 hours.My concern is in being able to clean up the .ldf files so they don't get too large on the production server. If I backup the database and truncate the logs does it cause a problem for that server when it attempts to ship logs the next time?Maybe I should ask this question... Does log shipping generate it's transaction logs from the database (.mdf) files or does it generate them from the tran logs (.ldf)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-28 : 18:53:57
|
| Truncating the logs will break log shipping plus your ability to restore to a point in time as you've broken the transaction log chain. To manage the size of the transaction log, all that you need to do is frequently back it up. We backup ours every 15 minutes. All transaction log backups (which log shipping uses) are done via the transaction log.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dholland
Starting Member
11 Posts |
Posted - 2008-03-28 : 20:58:16
|
| How will backing up my tran log (.ldf) without truncating it help manage it's size? (I'm not being difficult, I'm just new to this and trying to understand).. and thank you very much for your help thus far.-Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-28 : 21:13:51
|
| Well it only needs to grow if it is full. It empties out during each tlog backup. Truncate just empties it out but doesn't save the info, backup saves the info and then empties it out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-28 : 21:20:21
|
| Don't think about truncating log. Just do backup Log every 15 mins and you should be fine for managing growth of LDF files. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-29 : 02:01:45
|
| Didn't I already say that?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 11:56:13
|
| Yep I said that too. |
|
|
|
dholland
Starting Member
11 Posts |
Posted - 2008-03-29 : 13:05:32
|
| Oh now I think I understand what you are saying. The .ldf file will be a certain size (one of our DB's has a 20 gig .ldf) if I do a backup of that tran it will clear out the file however the .ldf will remain the same size.. it will just be empty - and will not grow unless it needs to - am I understanding correctly now? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 13:29:53
|
| You need to read about Transaction Log in BOL.It will explain clearly about active/inactive portion . |
|
|
|
dholland
Starting Member
11 Posts |
Posted - 2008-03-29 : 13:37:05
|
| What do you mean by "BOL" ? Either way I think I understand now what you guys are saying. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-29 : 14:39:14
|
| Yes your understanding is correct.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-29 : 15:01:47
|
Books online (BOL) |
|
|
|
skyw
Starting Member
8 Posts |
Posted - 2008-12-24 : 06:15:44
|
quote:
Originally posted by tkizer
Well it only needs to grow if it is full. It empties out during each tlog backup. Truncate just empties it out but doesn't save the info, backup saves the info and then empties it out.
Hi,I am facing the same issue. Please let me know if both - - scheduling the default job created by Log Shipping i.e. -LS_Backup_<DB Name> - to run every 15 mins
and- running the query for restoring LOG backup
..will have the same effect of emptying the .ldf file
Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-24 : 07:39:21
|
| Yes,It will manage Log growth. Also don't hijack thread .Please create new post for it next time. |
|
|
|
|
Question About Log Shipping