VDI Errors

mcetlinskimcetlinski Posts: 7
edited August 5, 2011 2:21AM in SQL Backup Previous Versions
I have updated SQL Backup to 6.5.1.9, and verified that my other backup servers are of the same version as well. I have added the VDITimeout Reg_DWORD and it's set to a hex of 120. With the above I still receive VDI errors.

I currently have 15 databases that are set up with Log Shipping across a private T1 link. The errors occur any time that I'm running 11 or more Log Ship restores at a time. In other words if I remove log shipping from a few databases I can get it to stop. Following is the log from one such failure. It varies as to which database it will fail the log restore on, but generally it's about 4 or 5 of them at a time:


-SQL "RESTORE LOG [ITAssets] FROM DISK = '\\pmssql1\backup\LOG_ITAssets_*.sqb' WITH ERASEFILES = 30, FILEOPTIONS = 1, MAILTO_ONERROR = 'mcetlinski@mwpms.com', NORECOVERY, MOVETO = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup' "
ERRORS AND WARNINGS


8/3/2011 11:47:18 AM: Restoring ITAssets (transaction logs) from:
8/3/2011 11:47:18 AM: \\pmssql1\backup\LOG_ITAssets_20110803114500.sqb

8/3/2011 11:47:35 AM: RESTORE LOG [ITAssets] FROM VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A92751', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275101', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275102', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275103', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275104', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275105', VIRTUAL_DEVICE = 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A9275106' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY

8/3/2011 11:47:35 AM: 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.
8/3/2011 11:47:35 AM: Also check that the database is not currently in use.
8/3/2011 11:47:35 AM:
8/3/2011 11:47:35 AM: SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID_1 REQUESTID
8/3/2011 11:47:35 AM:
8/3/2011 11:47:35 AM: 1 BACKGROUND sa . . RESOURCE MONITOR 0 0 08/01 08:17:13 1 0
8/3/2011 11:47:35 AM: 2 BACKGROUND sa . . XE TIMER 0 0 08/01 08:17:13 2 0
8/3/2011 11:47:35 AM: 3 BACKGROUND sa . . XE DISPATCHER 0 0 08/01 08:17:13 3 0
8/3/2011 11:47:35 AM: 4 BACKGROUND sa . . LAZY WRITER 62 0 08/01 08:17:13 4 0
8/3/2011 11:47:35 AM: 5 BACKGROUND sa . . LOG WRITER 655 0 08/01 08:17:13 5 0
8/3/2011 11:47:35 AM: 6 BACKGROUND sa . . LOCK MONITOR 15 0 08/01 08:17:13 6 0
8/3/2011 11:47:35 AM: 7 BACKGROUND sa . . master SIGNAL HANDLER 0 0 08/01 08:17:13 7 0
8/3/2011 11:47:35 AM: 8 sleeping sa . . master TASK MANAGER 0 0 08/01 08:17:13 8 0
8/3/2011 11:47:35 AM: 9 BACKGROUND sa . . master TRACE QUEUE TASK 0 0 08/01 08:17:13 9 0
8/3/2011 11:47:35 AM: 10 BACKGROUND sa . . master BRKR TASK 47 0 08/01 08:17:13 10 0
8/3/2011 11:47:35 AM: 11 BACKGROUND sa . . tempdb CHECKPOINT 1294 818 08/01 08:17:13 11 0
8/3/2011 11:47:35 AM: 12 BACKGROUND sa . . master TASK MANAGER 0 0 08/01 08:17:13 12 0
8/3/2011 11:47:35 AM: 13 sleeping sa . . master TASK MANAGER 0 0 08/01 08:17:13 13 0
8/3/2011 11:47:35 AM: 14 BACKGROUND sa . . master BRKR EVENT HNDLR 31 41 08/01 08:17:13 14 0
8/3/2011 11:47:35 AM: 15 BACKGROUND sa . . master BRKR TASK 0 0 08/01 08:17:13 15 0
8/3/2011 11:47:35 AM: 16 BACKGROUND sa . . master BRKR TASK 0 0 08/01 08:17:13 16 0
8/3/2011 11:47:35 AM: 17 BACKGROUND sa . . master BRKR TASK 0 0 08/01 08:17:13 17 0
8/3/2011 11:47:35 AM: 18 sleeping sa . . master TASK MANAGER 0 1 08/01 08:17:13 18 0
8/3/2011 11:47:35 AM: 19 sleeping sa . . master TASK MANAGER 0 0 08/01 08:17:13 19 0
8/3/2011 11:47:35 AM: 20 sleeping sa . . master TASK MANAGER 0 3 08/01 08:17:13 20 0
8/3/2011 11:47:35 AM: 21 sleeping sa . . master TASK MANAGER 0 0 08/01 08:17:13 21 0
8/3/2011 11:47:35 AM: 22 sleeping sa . . master TASK MANAGER 0 0 08/01 08:17:13 22 0
8/3/2011 11:47:35 AM: 23 sleeping sa . . master TASK MANAGER 0 11 08/01 08:17:13 23 0
8/3/2011 11:47:35 AM: 25 sleeping sa . . master TASK MANAGER 0 7 08/01 08:17:13 25 0
8/3/2011 11:47:35 AM: 26 BACKGROUND sa . . master BRKR TASK 0 0 08/01 08:17:13 26 0
8/3/2011 11:47:35 AM: 51 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 0 0 08/03 11:46:57 DatabaseMail - SQLAGENT - Id<8184> 51 0
8/3/2011 11:47:35 AM: 52 sleeping MWPMS1\Administrator PMSSQL1 . master AWAITING COMMAND 0 0 08/03 11:46:24 SQL Backup 52 0
8/3/2011 11:47:35 AM: 53 sleeping MWPMS1\Administrator GUINNESS . master AWAITING COMMAND 5180 15 08/03 11:41:52 Microsoft SQL Server Management Studio 53 0
8/3/2011 11:47:35 AM: 54 sleeping MWPMS1\Administrator PMSSQL1 . master AWAITING COMMAND 0 0 08/03 11:46:24 SQL Backup 54 0
8/3/2011 11:47:35 AM: 55 sleeping MWPMS1\Administrator PMSSQL1 . master AWAITING COMMAND 0 0 08/03 11:45:19 SQL Backup 55 0
8/3/2011 11:47:35 AM: 56 sleeping MWPMS1\Administrator PMSSQL1 . master AWAITING COMMAND 0 0 08/03 11:46:24 SQL Backup 56 0
8/3/2011 11:47:35 AM: 57 sleeping MWPMS1\Administrator PMSSQL1 . master AWAITING COMMAND 0 0 08/03 11:45:19 SQL Backup 57 0
8/3/2011 11:47:35 AM: 58 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 15 0 08/01 08:21:14 SQLAgent - Email Logger 58 0
8/3/2011 11:47:35 AM: 59 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 9960 6 08/03 11:47:20 SQLAgent - Alert Engine 59 0
8/3/2011 11:47:35 AM: 60 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 218 179 08/03 11:40:41 SQLAgent - Generic Refresher 60 0
8/3/2011 11:47:35 AM: 61 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 296 2264 08/03 11:47:00 SQLAgent - Job invocation engine 61 0
8/3/2011 11:47:35 AM: 62 sleeping sa GUINNESS . master AWAITING COMMAND 0 0 08/03 11:47:34 SQBCoreService.exe 62 0
8/3/2011 11:47:35 AM: 63 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 15 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0x410303F83616A84AA7A32B4DBA289378 : Step 1) 63 0
8/3/2011 11:47:35 AM: 64 sleeping MWPMS1\Administrator GUINNESS . master AWAITING COMMAND 675 14 08/03 11:10:03 Microsoft SQL Server Management Studio 64 0
8/3/2011 11:47:35 AM: 65 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 0 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0x1CECF403839CF948886D4D09D5D89C2C : Step 1) 65 0
8/3/2011 11:47:35 AM: 66 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 0 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0x5A5BB94C04696D47B8414BA349F9543D : Step 1) 66 0
8/3/2011 11:47:35 AM: 67 sleeping sa GUINNESS . master AWAITING COMMAND 0 0 08/03 11:47:35 SQBCoreService.exe 67 0
8/3/2011 11:47:35 AM: 68 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 0 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0xC330FC61993CAD4CA317F7C5C1DF4118 : Step 1) 68 0
8/3/2011 11:47:35 AM: 71 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 0 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0x8FCDC2C92E133C42B92AEAD004C36388 : Step 1) 71 0
8/3/2011 11:47:35 AM: 72 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 0 0 08/03 11:47:34 DatabaseMail - DatabaseMail - Id<6876> 72 0
8/3/2011 11:47:35 AM: 73 RUNNABLE MWPMS1\SQLServer GUINNESS . master EXECUTE 0 0 08/03 11:47:00 SQLAgent - TSQL JobStep (Job 0xA32F9D53AF1F33458984FA79F60B4A8F : Step 1) 73 0
8/3/2011 11:47:35 AM: 74 sleeping sa GUINNESS . master AWAITING COMMAND 15 0 08/03 11:47:23 SQBCoreService.exe 74 0
8/3/2011 11:47:35 AM: 75 sleeping sa GUINNESS . master AWAITING COMMAND 0 0 08/03 11:47:23 SQBCoreService.exe 75 0
8/3/2011 11:47:35 AM: 76 sleeping MWPMS1\Administrator GUINNESS . ReportServer AWAITING COMMAND 0 0 08/03 11:47:32 Report Server 76 0
8/3/2011 11:47:35 AM: 78 sleeping sa GUINNESS . master AWAITING COMMAND 0 0 08/03 11:47:24 SQBCoreService.exe 78 0
8/3/2011 11:47:35 AM: 79 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 0 0 08/03 11:47:32 DatabaseMail - DatabaseMail - Id<6876> 79 0
8/3/2011 11:47:35 AM: 80 sleeping sa GUINNESS . master AWAITING COMMAND 0 0 08/03 11:47:24 SQBCoreService.exe 80 0
8/3/2011 11:47:35 AM: 82 SUSPENDED MWPMS1\SQLServer GUINNESS . msdb DELETE 0 0 08/03 11:47:34 DatabaseMail - DatabaseMail - Id<6876> 82 0
8/3/2011 11:47:35 AM: 83 sleeping sa GUINNESS . master AWAITING COMMAND 16 3 08/03 11:47:34 SQBCoreService.exe 83 0
8/3/2011 11:47:35 AM: 85 RUNNABLE sa GUINNESS . master SELECT INTO 0 0 08/03 11:47:28 SQBCoreService.exe 85 0
8/3/2011 11:47:35 AM: 86 SUSPENDED sa GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 RUNNABLE GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 86 SUSPENDED GUINNESS . PanhandleSQL RESTORE LOG 0 0 08/03 11:47:29 SQBCoreService.exe 86 0
8/3/2011 11:47:35 AM: 87 SUSPENDED sa GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 RUNNABLE GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 87 SUSPENDED GUINNESS . JVCOSQL RESTORE LOG 0 0 08/03 11:47:30 SQBCoreService.exe 87 0
8/3/2011 11:47:35 AM: 88 SUSPENDED sa GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 RUNNABLE GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 88 SUSPENDED GUINNESS . OxTownSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 88 0
8/3/2011 11:47:35 AM: 89 SUSPENDED sa GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 RUNNABLE GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 89 SUSPENDED GUINNESS . LotTrackPMSSQL RESTORE LOG 0 0 08/03 11:47:31 SQBCoreService.exe 89 0
8/3/2011 11:47:35 AM: 90 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 0 0 08/03 11:47:32 DatabaseMail - DatabaseMail - Id<6876> 90 0
8/3/2011 11:47:35 AM: 91 sleeping MWPMS1\SQLServer GUINNESS . msdb AWAITING COMMAND 16 0 08/03 11:47:35 DatabaseMail - DatabaseMail - Id<6876> 91 0
8/3/2011 11:47:35 AM:
8/3/2011 11:47:35 AM: SQL error 3013: RESTORE LOG is terminating abnormally.
8/3/2011 11:47:35 AM: SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_2A6D3E8F-4782-4EC5-9236-8EC990A92751'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).
8/3/2011 11:47:35 AM:
8/3/2011 11:47:35 AM: Memory profile
8/3/2011 11:47:35 AM: Type Maximum Minimum Average Blk count Total
8/3/2011 11:47:35 AM:
8/3/2011 11:47:35 AM: Commit 704380928 4096 737045 1434 1056923648
8/3/2011 11:47:35 AM: Reserve 783745024 8192 8411559 121 1017798656
8/3/2011 11:47:35 AM: Free 4034560 4096 289624 251 72695808
8/3/2011 11:47:35 AM: Private 783745024 4096 1953350 938 1832243200
8/3/2011 11:47:35 AM: Mapped 29360128 4096 1430958 107 153112576
8/3/2011 11:47:35 AM: Image 34701312 4096 175228 510 89366528
8/3/2011 11:47:35 AM:
· Share on Twitter

Comments

  • mcetlinskimcetlinski Posts: 7
    For anyone else that runs into this issue I have found a work around for my scenario. It may actually be a better set up in the long run as it allows for fewer processes at a time.

    I have gone in and staggered my restores to occur at two minute intervals so that there are never any more than 3 log shipping jobs attempting to occur at a time. It's taken a bit of fine tuning and I'll have to watch it over the next few days/weeks to be sure I got it. Ultimately with the number of databases that I have it's almost a continuous string of restores that are being done.

    If anyone has any thoughts please contribute them! :)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    When SQL Backup performs a restore, SQL Server needs to allocate memory from its virtual address space (or the MemToLeave region). As your backup set contains 7 backup devices, by default, SQL Server would need to allocate 28 MB of memory from its VAS (SQL Backup allocates 28 buffers, each 1 MB in size). Most SQL Server installations allocate 256 MB of memory for its VAS, so running 8 or more restores simulatenously would certainly exhast the available VAS very quickly.

    One workaround is to set the MAXTRASFERSIZE value in the restore command. By default, SQL Backup allocates 1 MB of memory for each restore buffer. You can use any value from 64 Kb to 1 MB, as long as they are in increments of 64 Kb. Another option is to create a MaxTransferSize setting in the SQL Backup registry node, and assign it a value. This value would then be used as the default MAXTRANSFERSIZE value if none is explicitly provided in the BACKUP or RESTORE commands. You create this setting in the HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal node. Type is DWORD.

    Another workaround, as you have found out, is to reduce the number of simultaneous backups/restores ran by SQL Backup. You could combine all the RESTORE scripts into a single batch, to run each RESTORE sequentially. The disadvantage of this is that you would no longer be able to use the GUI to manage the job.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • mcetlinskimcetlinski Posts: 7
    What are the side effects of lowering the maxtransfersize? Would this increase time to complete each restore only, or are there are things to consider here?

    Thanks for the insight
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    You can add the -g startup parameter to increase your VAS memory.

    As Petey said SQLBackup needs contigous VAS memory. This can get fragmented easily so you may need to expand this.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • mcetlinskimcetlinski Posts: 7
    From looking at the -g option I can see the benefits of using it. I took a closer look on MSDN: http://msdn.microsoft.com/en-us/library/ms190737.aspx

    The only thing I'm a bit hesitant to do is to increase this too much. As it appears that I have alleviated the issue by staggering my processes it may be fine. As there is so much of our business running on the SQL databases I'm a bit hesitant to make many changes like this without understanding the upper and lower limits and what the possible issues I might see are.
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    What version of SQL are you running?

    if its 2005 or higher try running this when you are running SQLBackup

    Begin
    With VASummary(Size,Reserved,Free) AS
    (SELECT
    Size = VaDump.Size,
    Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 1 ELSE 0 END)
    FROM
    (
    SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
    AS Size, region_allocation_base_address AS Base
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address <> 0x0
    GROUP BY region_allocation_base_address
    UNION
    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address = 0x0
    )
    AS VaDump
    GROUP BY Size)


    SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [VAS Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
    FROM VASummary
    WHERE Free <> 0
    end

    It will tell you how much VAS is available and the biggest chunk.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • mcetlinskimcetlinski Posts: 7
    Much appreciated. SQL 2008 R2. Currently there is 172MB available, so I can see now that anything over 6 concurrent restores at a time would be problematic. Based on this I know I can go up a small amount on the VAS and be perfectly safe.

    Thanks for the information > on to a bit more fine tuning. :)
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Its more the max free size part that is important. I know that SQLBackup needs 6Mb contigous to run a backup. You might have over 100Mb available but if it gets fragmented and the max free is less than 6Mb it will either not run or push something out of the VAS. If you use CLR this is something else that goes into the VAS.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    ChrisAVWood wrote:
    Its more the max free size part that is important.
    That is true of SQL 2000 and older, but not in SQL 2005 and newer. From the VDI documentation on changes from SQL 2000:
    VDI shared memory segments may now be mapped at many different addresses. This has no impact on the functionality of a client, but should help in situations where virtual address limitations required tweaking the "-g" parameter of sqlservr.exe. Larger total buffer space should be available. In previous versions of VDI, a single contiguous allocation was required, which could preclude the use of large buffer allocation.
    You do still need free memory blocks that are at least MAXTRANSFERSIZE in size available, in the quantity specified by the BUFFERCOUNT value, but all the required memory need not be contiguous.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    mcetlinski wrote:
    Much appreciated. SQL 2008 R2. Currently there is 172MB available, so I can see now that anything over 6 concurrent restores at a time would be problematic.
    You can also use the SQL Backup sqbmemory function on any version of SQL Server to retrieve details of SQL Server's memory status e.g.
    EXEC master..sqbmemory
    This function is available on any SQL Server instance where the SQL Backup server components are installed.
    mcetlinski wrote:
    Based on this I know I can go up a small amount on the VAS and be perfectly safe.
    The more you allocate to the MemToLeave region, the less you have for your buffer pool. You also need to take into consideration that the MemToLeave region is also used by managed code, .NET CLR, linked servers and extended stored procedures. Any sudden spikes in usage of the above components may reduce the available free memory, causing restores to fail if you are running close to the limits.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.