Sql backup occasionally fails with memory errors
We are having a problem with our Redgate transaction log backup process with it occasionally erroring with various memory related issues
Error: 802, Severity: 17, State: 20. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'internal' to run this query.
Error: 17300, Severity: 16, State: 1.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
The machine is Sql Server 2008 SP2 running on Windows 2008R2 64bit SP1 with Redgate SqlBackup 188.8.131.52
I have checked all the obvious perf counters but can't seem to find anything that would indicate why this happens sporadically. I have a couple of questions:
1. I have read some forum posts about using master..sqbmemory to determine free memory and memory fragmentation, however having run this I'm not sure it is reporting it correctly:
Type Minimum Maximum Average Blk count Total
Commit 4096 243556352 288475 12794 3690758144
Reserve 8192 16711680 81235 11898 966537216
Free 4096 8785218568192 46762955645 188 8791435661312
Private 4096 243556352 187931 24089 4527087616
Mapped 4096 4145152 324204 66 21397504
Image 4096 47828992 202626 537 108810240
This would seem to indicate free memory of 8.7TB? This is not correct as the machine only has 4GB of physical memory. Am I reading this wrong?
2. Do you have any other suggestions for what we can do to either solve the problem, or diagnose the problem?