Load-based multiple restore errors?

marclallen

Hi!

I'm currently running 7.4.0.186, and I have a very annoying issue.

Every week I have a series of six DB backups that need to be restored to the same reporting/snapshot machine. All the backup files are on the same (but different from the target) networked machine.

SQL Backup allows up to four restores to be running at one. However, I often get the following problems when I try to get them running at once.

1/26/2014 9:03:04 AM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role; and that no other
processes are blocking the backup or restore process; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]
1/26/2014 9:03:04 AM: Also check that the database is not currently in use.

(I've done all the suggested VDITimeout solutions, but none of them work because, I think, of the later errors)

1/26/2014 9:05:27 AM: SQL error 3013: RESTORE DATABASE is terminating abnormally.
1/26/2014 9:05:27 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_7C19257A-80B8-4857-AE48-AFF278BDE9C3'. Operating system error 0x80070002(The system cannot find the file specified.).

Now, I'm not entirely sure what that final error means. Does it mean that the OS on the machine holding the backup file is reporting an error message? Does it mean the DB Server OS is reporting some issue with a virtual file? Because the file does exist. If I simply wait until one or more of the running restores are completed and then retry the restore, it works fine.

Any idea what might be causing this?

The databases in question are roughly 500GB in size.

Thanks,

Marc

Eddie D

Hi Marc,
Thank you for your forum post and sorry that you have encountered a problem. There are several situations that can cause SQL Backup VDI Error 1010.

Two of the most common causes are insufficient contiguous memory in the SQL Server Virtual Memory Space and VDITimeout. I will explain each one of these two causes:

Insufficient Contiguous Memory
SQL Server specifies an amount of virtual address space (in megabytes, default value is 128MB in SQL 2000 and 256Mb in SQL 2005 / 2008) that SQL Server will leave available for memory allocations within the SQL Server process but outside the SQL server memory pool. This area is used by SQL Server for loading extended procedure .dll files, such as xp_sqlbackup.dll used by SQL Backup. It is in this area of memory that SQL Backup creates the VDI device for use when running a backup or restore task.

Unlike the backup task, where the user can configure a parameter called MAXTRANSFERSIZE and the number of CPU threads that affect the amount contiguous memory used. Also in a backup task, when encountering a VDI error, SQL Backup will reattempt the backup job reducing automatically the value for the MAXTRANSFERSIZE parameter in case the error was caused by an insufficient amount of continuous memory, THIS DOES NOT OCCUR FOR A RESTORE TASK. On average SQL Backup requires approximately 6MB of continuous memory per thread (MAXTRANSFERSIZE x 6 x Number of Threads).

1 thread = 6MB
5 threads = 30MB
8 threads = 48MB
10 threads = 60MB
15 threads = 90MB
20 threads = 120MB
32 threads = 192MB (Highest number of threads that can be used)

When a backup is performed the MAXTRANSFERSIZE value and the number of threads used is recorded into the backup file header. This information is then used by the restore process.

Therefore in your environment when you attempt to run the 5th restore job, you may have insufficient contiguous memory. If this is the case, investigate either reducing the number of threads used to create the backup file or the MAXTRANSFERSIZE value or combination of both settings.

VDITimeout
When SQL Backup issues the backup or restore command to SQL Server, SQL Backup creates the Virtual Device Interface (VDI) and waits for SQL Server to respond. If SQL Server does respond within 30 seconds, SQL Backup generates VDI Error 1010.

The delay could be caused by many things e.g. a blocking process such as another backup of the same database, or a CPU intensive task that's running and has tied up the server.

As you have discovered you can set a Registry Key called VDITimeout which increases the time that SQL Backup waits for SQL Server to respond to the backup or restore request.

I do not know what value you have specified for the VDTImeout registry key but I suspect that you need to increase the value of between 180 to 300 seconds, based on the log file information in your post.

Assuming that the restore command was issued at 9:02:34. SQL Backup waited 30 seconds before creating the VDI 1010 error:
1/26/2014 9:03:04 AM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed...........
Approximately two and half minutes later, SQL server responds to the restore request but the VDI is no longer available and this entry in the log file is created:
1/26/2014 9:05:27 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_7C19257A-80B8-4857-AE48-AFF278BDE9C3'. Operating system error 0x80070002(The system cannot find the file specified.).

A support call has been created for you HERE, to view or update the call either reply to this forum post or directly to the support call. If you have not already done so, you will need to create a RedGate ID to view the support ticket in our call logging system.

I hope the above helps.
Many Thanks
Eddie