Timing of Log Shipping Transaction Log Restores

geoff.tannergeoff.tanner Posts: 8
edited September 13, 2010 6:46PM in SQL Backup Previous Versions
I have about 33 databases that I want to log ship. Seems like a lot, but they are required. I have set up 23 of the jobs on a 15 minute interval. On the whole the process works but I am starting to get failures due to
VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed
.

I will institue a VDITimeout registry key s per http://www.red-gate.com/supportcenter/C ... 000250.htm but I wanted to better understand why, using a single schedule, this is an issue on the restore and not the backup. Is it simply reflecting that restores take more resources than backups?

Is there any other way of controlling the way the SQL Agent tries to process these jobs so they execute mre sequentially?

Thanks
Geoff Tanner
Geoff Tanner
Lead SQL DBA
Wishlist
Port Melbourne Australia
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    this is an issue on the restore and not the backup. Is it simply reflecting that restores take more resources than backups?
    If you look at the CPU utilization on your standby server, does it peak at 100%?
    Is there any other way of controlling the way the SQL Agent tries to process these jobs so they execute mre sequentially?
    One option may be to use multiple job steps, where each job step runs sequentially and restores the transaction logs for a specific database, instead of using multiple jobs all running simultaneously.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • geoff.tannergeoff.tanner Posts: 8
    Pete, thanks for the post. CPU gets to about 58% for about 5 seconds during the log restore. The databases are all fairly small - not many transactions - so the entire 23 DBs take about the 5 to 10 seconds to restore. On the job step theme, I could do that. I am a bit disappointed to have to take that step though, as the whole idea for me was to let SQL Backup manage the required jobs and schedules. Do people not usually ship logs for so many databases?
    Geoff Tanner
    Lead SQL DBA
    Wishlist
    Port Melbourne Australia
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If it takes only 5 to 10 seconds to restore the transaction logs for all 23 databases, I think the problem may be due to something else, as the default VDI timeout in SQL Backup is 30 seconds.

    Could you please post the entire contents of the SQL Backup log for the restore process that failed? The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • geoff.tannergeoff.tanner Posts: 8
    here is a sample of the error message I get when the shipping fails:
    SQL Backup log file 6.4.0.56

    -SQL "RESTORE LOG [LMPOS_KitchenWarehouse] FROM DISK = '\\svr-dbase02\LogShipping\LOG_LMPOS_KitchenWarehouse_*.sqb' WITH ERASEFILES = 2, FILEOPTIONS = 1, MAILTO_ONERRORONLY = 'AllDBATeam@vii.com.au', NORECOVERY, MOVETO = 'F:\MSSQL\LogShipping\Processed' "
    ERRORS AND WARNINGS


    9/09/2010 3:02:01 PM: Restoring LMPOS_KitchenWarehouse (transaction logs) from:
    9/09/2010 3:02:01 PM: \\svr-dbase02\LogShipping\LOG_LMPOS_KitchenWarehouse_20100909150001.sqb

    9/09/2010 3:02:01 PM: RESTORE LOG [LMPOS_KitchenWarehouse] FROM VIRTUAL_DEVICE = 'SQLBACKUP_4FE9B074-E82F-4D93-8A4A-32B506BE39D0', VIRTUAL_DEVICE = 'SQLBACKUP_4FE9B074-E82F-4D93-8A4A-32B506BE39D001' WITH BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY

    9/09/2010 3:02:01 PM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. SQL Backup required 0 bytes of free SQL Server memory, which was not available. VDI error: An abort request is preventing anything except termination actions.
    9/09/2010 3:02:01 PM: Also check that the database is not currently in use.
    9/09/2010 3:02:01 PM: SQL error 3013: RESTORE LOG is terminating abnormally.
    9/09/2010 3:02:01 PM: SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_4FE9B074-E82F-4D93-8A4A-32B506BE39D0'. Operating system error 0x8007000e(error not found).
    9/09/2010 3:02:01 PM:
    9/09/2010 3:02:01 PM: Memory profile
    9/09/2010 3:02:01 PM: Type Maximum Minimum Average Blk count Total
    9/09/2010 3:02:01 PM:
    9/09/2010 3:02:01 PM: Commit 1073414144 4096 1843283 1668 3074596864
    9/09/2010 3:02:01 PM: Reserve 4190208 4096 264796 278 73613312
    9/09/2010 3:02:01 PM: Free 37814272 4096 325668 224 72949760
    9/09/2010 3:02:01 PM: Private 1073414144 4096 2135568 1372 2929999872
    9/09/2010 3:02:01 PM: Mapped 12582912 4096 1093139 133 145387520
    9/09/2010 3:02:01 PM: Image 24866816 4096 165131 441 72822784
    9/09/2010 3:02:01 PM:
    9/09/2010 3:02:01 PM: Mail sent successfully to: AllDBATeam@vii.com.au
    Geoff Tanner
    Lead SQL DBA
    Wishlist
    Port Melbourne Australia
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The problem is due to the lack of free memory in the SQL Server address space to service the VDI request. It looks like you're backing up using 2 threads, so by default, each restore will require 2 MB of free memory from SQL Server.

    You can reduce this requirement by lowering the requested VDI buffer size. One way to do this is by using the MAXTRANSFERSIZE option e.g.
    -SQL "RESTORE LOG ... FROM WITH ..., MAXTRANSFERSIZE = 65536

    Minimum value is 65536, maximum is 1048576 (default), and the value needs to be in increments of 65536 bytes. A lower buffer size may result in slower restore throughput, but since your transaction logs aren't that large, the effect is probably immaterial.

    Unfortunately, you'll need to add this option manually as it isn't available in the GUI. If you would prefer not to do this, you can add a registry value, MAXTRANSFERSIZE, to the SQL Backup global settings i.e.

    HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

    which will be used by all SQL Backup backup and restore processes for that SQL Server instance. The value MAXTRANSFERSIZE is of the REG_DWORD type.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • geoff.tannergeoff.tanner Posts: 8
    Peter, thanks for this post, I applied the MAXTRANSFERSIZE setting to the restore jobs and they are all succeeding now. I appreciated your rapid response and accurate answers.
    Geoff Tanner
    Lead SQL DBA
    Wishlist
    Port Melbourne Australia
    · Share on Twitter
Sign In or Register to comment.