Sql backup occasionally fails with memory errors

isaacmcn · November 9, 2011 3:12PM

Hi there,

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 6.4.0.56

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?

Thanks,

Isaac

peter.peart · November 11, 2011 11:21AM

Thanks for your post. The memory allocation you are seeing here is consistent with virtual memory allocation for a 64bit OS per the below on MSDN:

http://msdn.microsoft.com/en-us/library/ms187499.aspx

Essentially, the problem is the amount of memory on the box. You need more than 4GB really if you're running a 64bit OS and 64bit version of SQL Server. You are probably also in the situation where SQL Server itself is set to use all the memory it can, and between that and the OS I would expect more often than not the two of them are nearly consuming all of that 4GB.

You've also got to bear in mind that even if the 4GB is not all being used, you may have fragmented memory which will only be solved with a server restart.

HTH!

Pete

isaacmcn · November 13, 2011 3:52PM

Hi Pete, thanks for the response.

A couple of thoughts:

1. I didn't realise that more than 4GB was recommended when using 64bit OS and 64bit Sql. Is that a general recommendation, or suggested when also running Redgate Sqlbackup?

2. I had wondered about specifying the max memory setting in Sql, I always thought that Sql would release memory to the OS as required but further reading suggests this is only the case if the OS is desparate. Just a question about Redgate Sql Backup then - how much memory does the SQBCoreService require separate from the SqlServer service? Looking at perfmon I'd guess that the SQBCoreService is doing the compression/encryption of backups and writing to disk, so would it pay to dial Sql's max memory back to leave space for SQBCoreService? (e.g. if we bump the physical mem up to 6GB then specify Sql's max memory as 5GB would that be sufficient)

3. Regarding memory fragmentation - is the memory it's trying to use internal or external to Sql (i.e. are we concerned about Sql's memory being fragmented or the OS's?)

4. We have another server running SqlBackup on much larger databases and haven't hit the same problem. It doesn't have the max memory setting configured, but has 8GB ram. Is it just the fact that it has more physical memory that it doesn't have the same problem?

Thanks again for your help

Isaac

peter.peart · November 14, 2011 10:23AM

Hi Isaac,

Thanks for your reply. I'll answer as you've laid out:

1) I don't know if there's anything written down anywhere per se, however since 64bit systems are designed to use more than 4GB if they can, I would personally generally advise people to make sure there's more than 4GB available.

2) The SQBCoreService.exe is a 32bit app, so it will never exceed 4GB. Personally, I've never seen it using more than about 500MB and we've never had anyone that I'm aware of complain about the memory usage.

3) SQL allocates memory for VDI operations, but that isn't to say the overall memory isn't fragmented. We've always advised, when this is seen as being a likely cause, to schedule in a complete server reboot. This has on every occasion resolved the issue when fragmented memory has been the problem.

4) I would have though so, yes. It kinda of backs up my first answer.

HTH!

Pete

isaacmcn · November 14, 2011 3:43PM

Thanks for your help Pete, we will look at implementing your recommendations and see if that resolves the problem.

Cheers,

Isaac

Sql backup occasionally fails with memory errors

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk