0

I'm trying to limit the size of individual files that are transferred as part of log shipping (as I am using WebDAV for transport).

I want to ensure that no trn files are created that are more than a certain size (e.g. 16MB) as it seems to choke WebDAV.

I note from the output of sqllogship.exe, that it states:

Max Transfer Size: Not Specified

Can this be configured?

Thanks Andy

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
Andy Joiner
  • 5,932
  • 3
  • 45
  • 72
  • 1
    The max transfer size seems to be dealing with how the files are read on the secondary server, rather than the size of the transaction logs. How could it be otherwise? You've set a schedule for when transaction log backups should occur. If it hit it's size limit, what's the database meant to do? Refuse service until the next transaction log backup occurs? (Max transfer size parameter: http://msdn.microsoft.com/en-us/library/ms188916.aspx) – Damien_The_Unbeliever Feb 18 '11 at 09:57
  • I was rather hoping that it might split very large backups into chunks no larger than a certain size for easier transfer. – Andy Joiner Feb 18 '11 at 11:40

1 Answers1

1

I don't think what you're trying to achieve is possible (and Damien_The_Unbeliever's comment seems to back this view up).

As far as I can see, the only approach you can take is to schedule you log backups frequently enough that the file size limit isn't reached.

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • Thanks! I'm already running backups at 1-minute intervals, but occasional DB maintenance makes large changes in a short space of time. – Andy Joiner Feb 18 '11 at 11:45
  • Reindexing can bloat the log file, and as your database and indexes get bigger, so will the log file. Eventually, you may find yourself unable to keep the log under your desired size no matter how frequently you backlog the tlogs. – Larry Silverman Mar 03 '11 at 22:23
  • Maybe you could zip the log files or write some custom code to split them up to a manageable size? And also investigate bulk-logged recovery mode too. – Rocklan Feb 16 '12 at 12:45
  • @LachlanB - beware that benefits from using bulk-logged recovery may be minimal, depending on the workload. See http://msdn.microsoft.com/en-us/library/ms191244.aspx – Ed Harper Feb 16 '12 at 14:44