I have a number of SQL 2008 instances all running Microsoft SQL Server 2008 (SP4) (confirmed with
select @@VERSION on the servers in question). They run on either Windows Server 2008 or Windows Server 2008 R2.
Two of them exist solely to log ship with Red Gate SQL Backup 18.104.22.168, and I'm having issues with one of them. (It's one of the 2008 R2 servers, if that makes a difference.) I'm using a t-sql job that scrolls through a very long list of databases (dynamically pulled from the other servers) and restores them.
Previously, this job took less than 10 minutes. It's now taking an hour and a half to two and a half hours. There have been no code changes and no radical increases in the number of databases to restore. Its sibling server, with almost identical code, is running this job in under 4 minutes. (The sibling server is one of the non-R2 servers, if that makes a difference.)
The Event Log and SQL Error log show an error of:
Operating system error 0x80770006 (failed to retrieve text for this error. Reason: 317)."
I don't know if this is the cause of the problem or not; Google suggests that this occurs when different versions of SQL Server coexist, or Red Gate SQL Backup 6.x needs a special patch. I don't think either of these are the issue because the error is intermittent, the SQL Server versions are identical, and I'm running Red Gate SQL Backup 7.x, but I could certainly be wrong. Red Gate forums suggested running a query to see if VAS memory was low, since that could cause similar issues.
VAS Total avail mem, KB Max free size, KB 8320072080 8314974784
Other things I've tried to resolve the issue include:
- Cleaning old log files out of "C:\ProgramData\Red Gate\SQL Backup\Log[instancename]", because the last time the job slowed down it was because there were too many log files in that directory.
- Checking for and resolving any memory issues on the server.
- Making sure that antivirus has exclusions for .sqb files (SQL Backup).
CHKDSKon the volumes involved.
- Watching the job run with
- Checking msdb to make sure that the purge job was running properly. The oldest entry is four weeks old, but it still seems overlarge.
DBCC CheckDBon msdb.
- Asking those with visibility into the storage utilities to check for any failures there. They say my storage is "optimal."
Things I plan to do:
- Purge msdb's history to be only a week. It's a blocking query so I want to wait until after hours, even though customers don't actively query this instance.
Watching the job run with
sp_whoisactive seems to show a lot of
EX) on msdb, but the waits are usually less than a second. (The query is the updating backupset procedure.)
The only error I can find is intermittent variants of (from the SQL Error log):
2016-05-25 14:12:39.18 Backup Error: 3201, Severity: 16, State: 7. 2016-05-25 14:12:39.18 Backup Cannot open backup device 'SQLBACKUP_D99ABDE1-42E6-4617-B1EB-BDA30BF8113B'. Operating system error 0x80770006(failed to retrieve text for this error. Reason: 317).
(followed immediately by "Log was restored.") and (from Event Viewer application log):
SQLVDI: Loc=SVDS::Open. Desc=Bad State. ErrorCode=(-1). Process=8056. Thread=10512. Server. Instance=DR. VD=Global\SQLBACKUP_D99ABDE1-42E6-4617-B1EB-BDA30BF8113B_SQLVDIMemoryName_0. Cannot open backup device 'SQLBACKUP_D99ABDE1-42E6-4617-B1EB-BDA30BF8113B'. Operating system error 0x80770006(failed to retrieve text for this error. Reason: 317).
What am I missing? Where else can I look?
Things I did after hours:
- Purge msdb to a week.
- Add a registry key for the VDI timeout for Red Gate SQL Backup. (I'd changed this value previously and then deleted the key. The default is 30 seconds and I thought one database seemed hung up a lot longer than 30 seconds, so I put in a key with the default value to be sure.)
No difference, but I did find this query and it seems to have given me a lead. One database in particular that I thought I noticed in
sp_WhoIsActive taking a long time, well. It wasn't my imagination. Approximate restore times include 5068100, 4252443, 4408026, 2184080, 2786363 (in addition to things like 330, 373, etc.). (Those are Milliseconds.) I checked the number of VLFs in this database, and there are only 46 so something else is up with it.
I'm going to load up a full list of log ship DBs and run it again.
The secondary databases are in restoring, not standby. We're using Red Gate for the log shipping for the compression, because we have copies of the encrypted backups written to a share on that server already, and because there was concern about possible overhead on the master. There are a lot of databases being shipped. Over 800 on that server. I try to do it as one process to cut down on msdb contention.
The machines are bare metal, not VMs. It's just falling behind, as far as I can tell, and the contention is either the restore or writing information about the restore to MSDB (or both). The most recent restore was within the last minute.