SQL Backup 10 GUI/Restore Timeouts

DBNewbie_2007

We have been experiencing problems with one of our SQL Backup Pro instances, version 10.0.12. 
• Trying to do a restore via the GUI is extremely slow and connections in general are slow. 
• We experience periodic errors: 
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
• We bumped up the Connection Time-out from 15 to 90. Execution time-out is set to default of "0". 
• System should have enough resources... there are over 104 GBytes of RAM free. 
• SQL Server 2017 is the most recent version, 14.0.3223.3. 
• Running a "filelist only" query on a database we are trying to temporarily restore, 
    Type: SQL Backup 7 file
    Compression Level: 3
    Native backup size: 126.334 GB
    Database size: 311.442 GB
    Compression ratio: 85.65%
took 29 minutes:   

USE Master 

GO 

DECLARE @SQL VARCHAR(max), 

@FileName NVARCHAR(200);

SET @FileName = '\\10.1.10.10\Backups\Prod\Apr2017\MyBackup_BACKUP_FULL_20170409_014535.sqb'

SET @SQL = ' -sql "RESTORE FILELISTONLY FROM DISK = '''+@FileName+'''" '

EXEC master..sqlbackup @SQL

• We have a full backup running in the background as a scheduled job. 
• There are periodic log backups running, but none were active while experiencing these connection issues. 
• This is a very large server (data warehouse). All of the primary drives are SSD. Drives on the remote storage housing the backups are hybrids. 
• None of our other SQL Backup Pro instances appear to be having this issue on other servers. 

DBNewbie_2007

FYI, I also ran this: 
EXECUTE master..sqbmemory

Results: 

Type        Minimum     Maximum          Average        Blk count   Total

Commit    4096        33865138176      234573        4871073      1142622560256

Reserve   4096        1203238535168    509933        4848140      2472231276544

Free      4096        134915153526784  188614352755  727          137122634452992

Private   4096        1203238535168    371940        9717951      3614502526976

Mapped    4096        67108864         295756        320          94642176

Image     4096        29757440         272470        942          256667648

Eddie D

Hi, thank you for your support request.

The RESTORE FILELISTONLY task simply reads the file header of the backup file specified and generates information regarding the files for the database that created the backup file.

The bottlenecks with either be the network connection to the backup file location and locating specified backup file.  Or the workload of the SQL Backup Agent (SQBCoreService.exe).

You highlighted in your post there was a scheduled full backup in progress and periodic log backups. Concentrating on the log backups, do your log backup make use of the COPYTO keyword?

By default, log backup jobs that include the COPYTO keyword in the syntax, the COPYTO process operates in a different manner to full and differential backups COPYTO process.

Full and differential backups that include the COPYTO keyword, the COPYTO process occurs within the job.  In that once the backup file is successfully created, the copy process is performed before the job completes.

For log backup jobs, the COPYTO process takes place outside of the log backup job.  Once the log backup file is successfully created, an entry is added to a component called the Log Copy Queue and the job completes.  The SQL Backup Agent service monitors the Log Copy Queue and will copy the backup file to the destination specified in the COPYTO keyword outside of the log backup job.

The Log Copy Queue has been known to become a bottleneck particularly if you have several log backups waiting to be copied.

Using the SQL Backup GUI ->Log Copy Queue tab.  Do you have several entries in the Log Copy Queue indicating that the Log Copy Queue maybe the cause of the performance problem?  If the answer is yes, consider adding the USESIMPLECOPY keyword to your log backup jobs.

The USESIMPLECOPY keyword, will revert the log backup task using the COPYTO keyword to operate in the same manner as for full and differential backups.  Further reading available here on the Log Copy Queue.

Many Thanks
Eddie

DBNewbie_2007

Thanks for the update. I was waiting for a few days for some of the backups to complete, as one of the DBAs accidently changed the full backup jobs from 8 threads to 32 threads. 

After the backups completed last week (and nothing else was running from a backup standpoint), we are still getting the "Execution Timeout Expired" when trying to use the SQL Backup GUI (just trying to start the GUI up and review jobs, etc.). I tried again today (with a full backup running, 8 threads this time). 

Regarding the log backups, we backup directly to the NAS (another server on the network with Dell Compellent attached). We are not using the "COPYTO" option.

DBNewbie_2007

I tried to re-submit a ticket for this, as the problem has not been resolve and this is now causing serious issues, since we are unable to schedule or perform additional backups/restores.