April 2, 2009 at 3:55 pm
Hi All,
We have some huge jobs running on sql server db 2005, which create transaction log and this log is backedup up every 15 min and copied over WAN and restored on the second database.
For simple jobs - this works fine ..creates log backup of size 300/350/405 MB.
But for some big jobs which inserts millions of rows, the backup file size generated is huge ...5/10/18 gigs which become bottle neck to be copied over the WAN. And the Server become unsync.
Can we restrict the backup file size someway...I mean making the backup job frequency every 3-5 min instead of 15min and copy frequency every 10min. Will this reduce the backup log file size for log shipping ...
Tips and comment Appreciated
Thanks for your time
SLC
April 2, 2009 at 4:11 pm
unless the large inserts are one transaction more frequent backups will reduce the size of the transaction log backups
another alternative is to set up an alert so that the log backup job is kicked off when the tran log reaches a certain percentage full.
As long as the transaction log backups are successfully copied over the secondary database will catch up.
---------------------------------------------------------------------
April 2, 2009 at 4:21 pm
Hi,
There is no way in sql server where you can restrict the backup file size. One way is reduce the transactionlog backup schedule from every 15 mins to 5 mins as you said but it impacts your server performance. As you are using logshipping why dont you try your own customised logshipping where you can take the backups using the thirdparty tool like litespeed which reduces the backup size to almost 40%. Other option is you have to disable the backup and restore jobs and create new jobs which will take care of backups using litespeed and restore using litespeed. Above all these I recommend you to contact your network admin may be he can help you out about the network configuration and data transfer rate
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 2, 2009 at 4:30 pm
shahajic (4/2/2009)
Hi All,We have some huge jobs running on sql server db 2005, which create transaction log and this log is backedup up every 15 min and copied over WAN and restored on the second database.
And also as a best practice have you got your backup files stored locally?
For simple jobs - this works fine ..creates log backup of size 300/350/405 MB.
But for some big jobs which inserts millions of rows, the backup file size generated is huge ...5/10/18 gigs which become bottle neck to be copied over the WAN. And the Server become unsync.
Are you committing the inserts more frequently may be you can see that your tlog will not grow that huge?
Did you think of compressing these backup files, copy them over and then apply them to the secondary? or does it become too much of a hassle?
April 2, 2009 at 5:20 pm
You should consider using SQL Litespeed to compress the size of you transaction log backups.
April 2, 2009 at 6:33 pm
Litespeed, SQL Backup (Red Gate) or Hyperbac, all will help you compress the size of the backups and speed up the transfers (less disk I/O) with smaller files.
However, depending on how your jobs work, you may or may not get smaller log files. If there are large commits, backing up every minute might result in 14 small files, and 1 that's just as large. Depends on how the transactions are committed in your job.
April 2, 2009 at 7:58 pm
shahajic (4/2/2009)
Hi All,We have some huge jobs running on sql server db 2005, which create transaction log and this log is backedup up every 15 min and copied over WAN and restored on the second database.
For simple jobs - this works fine ..creates log backup of size 300/350/405 MB.
But for some big jobs which inserts millions of rows, the backup file size generated is huge ...5/10/18 gigs which become bottle neck to be copied over the WAN. And the Server become unsync.
Can we restrict the backup file size someway...I mean making the backup job frequency every 3-5 min instead of 15min and copy frequency every 10min. Will this reduce the backup log file size for log shipping ...
Tips and comment Appreciated
Thanks for your time
SLC
Hi Shahajic,
Would like to know what will be the state of the database on secondary server is that is in standby mode or no recovery mode upon restoration of transaction log backup files.
Also can you please let me know from your logshipping monitor which is lagging behind copying file or last restored file?
April 3, 2009 at 3:08 am
do litespeed or sql backup provide a complete logshipping solution, or do you have to write your own?
---------------------------------------------------------------------
April 3, 2009 at 10:01 am
shahajic (4/3/2009)
Would like to know what will be the state of the database on secondary server is that is in standby mode or no recovery mode upon restoration of transaction log backup files.=> The Secondary Server is in Standby/Read Only Mode
When database is in standby mode, when applying /restoring transction log backups which were taken during midst of transaction will take long time to restore transaction log backup file. Reason being it has to bring the database in standby / readonly mode.
When database is brought in standby mode, all the uncommitted transactions that are taking during backup or from previousbackups (if it is long, big and single transaction) will be written to a TUF File(Transaction Undo Format). So this file will be used when subsequent transaction log backup file is restored. When restoration of next backup file happens what it does is it parallely restores data from tlog backup file and reads all uncommited data from tuf file and then commits data.
So standby mode is really time consuming, if there is big transaction is going on and backups are taken during the transaction.
Now I would like to know does this happens only once during a day or pretty regular.
Do you need this database in standby mode?
Also can you please let me know from your logshipping monitor which is lagging behind copying file or last restored file?
=> Becuase of the huge backup log file size created on the Primary Server (eg : 5/10/18 GB) the Copy over WAN Network to the secondery server
I don't think so this is the major problem for logshipping going out of sync.
lags behind
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply