July 8, 2011 at 7:23 am
Hello,
I have a client for whom I configured log shipping.
Log shipping is only performed during certain nightly hours.
(The replicated db is used for very large reports to relax the workload of a very busy db)
The main DB is regularly backed up and is in RAID mirror configuration, so it's log file is pretty much useless for our intents and purposes.
The shear number of daily transactions grows the log file about 6GB per day when in full recovery mode.
I need to schedule log file shrinking.
The question is will it "break" the log shipping if scheduled to perform when no log shipping (or any work for that matter) is occurring?
July 8, 2011 at 7:45 am
Shrinking should be done in exceptional circumstances, not on a regular basis. All that regular shrinks will do is hinder overall performance as the log will have to grow again. Unless you've got the growth increments set carefully that will cause log fragmentation which will slow down backups, slow down recovery.
Why do you consider a 6GB log file to be a problem?
If you want to keep that down, make the log backup (part of log shipping) more frequent. If you only take backups at certain intervals the log is going to be huge. Log backups don't have a huge impact.
p.s. RAID mirroring is not a form of backup. I've seen enough RAID arrays fail and people left in serious trouble because they thought that it couldn't.
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
July 8, 2011 at 8:32 am
Thank you. You are right, nothing is 100% but the shrinking of log file would be done nightly, after both log shipping and full backup are finished, and before the daily load starts.
6GB is not much but very soon -- within a week -- it's over 40gigs and that is a problem.
July 8, 2011 at 8:37 am
Doesn't change a single thing that I said.
Run the log backups all day. 15 min is a common frequency. Don't shrink on a regular basis. The log, if managed correctly, will settle down to a steady size, that's the size that it needs to be for regular operation.
Regular shrinks will just cause the log to grow again as soon as activity starts. The growth will slow down any operations on the DB as the log has to zero-initialise. Furthermore repeated shrink/grow is going to cause file-level fragmentation and internal fragmentation that will slow down recovery and backups as well as other operations.
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
July 8, 2011 at 8:39 am
Also, this is not clear to me.
Does the log file get truncated when log shipping backup is taken and restored to LS db?
July 8, 2011 at 8:40 am
Ok thank you.
July 8, 2011 at 8:41 am
Log backups mark the space within the log as reusable. The restore on the secondary is irrelevant, it's the backup of the log that does that.
Please read through this - 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
July 8, 2011 at 8:58 am
Thanks once more, also for the link.
This is what you get when you make app programmer do a DBA-s job 🙂
July 10, 2011 at 12:59 pm
you can increase the frequency of the LS backup job without issues.
You could run the backup job every 15 mins as Gail suggested and the copy and restore jobs once each night if you wish. Just means the secondary database is only updated once a day. The schedules for all 3 jobs are definable
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 2:02 am
Hi,
Run ur LS_BACKUP up job for two times then shrink log file
if it couldn't then Run LS_BACKUP job one more time and shrink
Regards
Zishan
July 11, 2011 at 3:09 am
dont shrink, just increase the log backup frequency is the message here!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 6:57 am
we have found (from experience) that if you shrink the log of a logged shipped database that it changes the LSN's making the next TX log none restorable on the secondary server.
this was back in SQL 2000, we had a log drive of 50GB and a batch operation caused the log to jump up to 45GB within the 15 minute backup window.
was not happy when it took me all day to figure out what had happened and found it was down to the shrinkfile operation.
we all learn from our mistakes
July 11, 2011 at 7:28 am
Hi, it turns out, the problem was with the re-indexing job that went on nightly. Before log shipping was set up it was running for years, but it changed the db to simple recovery then re-indexed it then changed it back to full recovery. In order for it not to mess with LS I modified the job to no longer switch between the modes. And IT caused log file to grow uncontrollably.
With some more investigating, it turns out re-indexing was made a job due to some app problems that were resolved in the meantime, so there was really no need to do full re-index nightly. I disabled this job, now LS works just fine and log file is of normal size.
Thank you all for your input, it helped me learn how log file works in MSSQLS, a matter I was not very familiar with.
Cheers
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply