July 3, 2011 at 6:53 pm
Hi Friends,
I'm experiencing a very annoying and hard to fix problem with my SQL2008, housed on Windows Server 2003 R3 Enterprise. Every couple of days my maintenance plans are failing with the following error message
'There is insufficient system memory in resource pool 'internal' to run this query. BACKUP LOG is terminating abnormally'
If i restart the server then it will resolve but within a few days it pops its ugly head up again. The machine has 4gb Ram and this problem has only just started happening.
Has anyone else come across this before???
Many thanks 🙂
Mr J
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
July 3, 2011 at 7:28 pm
Is this an x64 system?
If so, did you set lock pages in memory right and also set max memory for SQL Server?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 3, 2011 at 7:47 pm
Hi Jeffrey,
The server is SQL Server 2008 SP1 CU5, max memory is set at 4096Mb as for set lock pages can you explain further? Do you mean AWE?
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
July 4, 2011 at 12:43 am
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
July 4, 2011 at 2:39 am
Mr J (7/3/2011)
Hi Jeffrey,The server is SQL Server 2008 SP1 CU5, max memory is set at 4096Mb as for set lock pages can you explain further? Do you mean AWE?
Cheers 🙂
Mr J
Jeffrey asking the sql server version 32 bit or 64 bit
Post me the results
select @@version
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 2:42 am
Lock Pages in Memory has to be enabled especially for 64 bit so that OS doesn't page SQL Server out.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 2:50 am
muthukkumaran Kaliyamoorthy (7/4/2011)
Lock Pages in Memory has to be enabled especially for 64 bit so that OS doesn't page SQL Server out.
Hi Muthu,
I found the the lock pages in memory is not required for 64 bit OS from the below link.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Could you please explain little bit about the conflict?
July 4, 2011 at 3:08 am
Found an article which is very interesting!!!
July 4, 2011 at 9:33 am
sqlzealot-81 (7/4/2011)
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspxFound an article which is very interesting!!!
The question I asked has nothing to do with whether or not this is required. It has to do with whether or not it was set at all.
On x64 hardware - if you set the lock pages in memory right (or run under local administrator, which has that right), you really must set max memory for SQL Server or you will run into memory problems when SQL Server takes all of the memory on the system.
Even without this option set, on x64 hardware it is possible for SQL Server to take all available memory and starve the OS.
Either way, the OP has 4GB of memory available and has allocated 4GB of memory to SQL Server. SQL Server is using all of that memory and starving the system - which is probably leading to the errors he is getting.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 4, 2011 at 6:00 pm
Hi Muthukkumaran,
Thanks for your reply it's 32bit or to get technical -
Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86) Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
I have a sneaking suspicion its because i have 2008 on an 32bit environment and it's leaking memory or not enough is available for the core components of sql.
---****----
Sqlzealot, thanks heaps for the article i really learn alot and feel that if i add the sql user account to the lock pages permissions this could(fingers crossed) fix the problem.
Do you guys think this is a sound course of action??
Many thanks
Mr J 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
July 5, 2011 at 3:35 am
Mr J (7/3/2011)
Hi Jeffrey,The server is SQL Server 2008 SP1 CU5, max memory is set at 4096Mb as for set lock pages can you explain further? Do you mean AWE?
Cheers 🙂
As you said you set 4GB as max memory. What is the total memory the system has?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 5, 2011 at 7:37 am
Hi Muthukkumaran,
The box itself has 24gbs all up, its running multiple instances, 4 of 2005 with max memory set between 4Gb and 2Gb and 1 2008 instance with 4gb(the server with the mem problem). All up the OS has about 10Gb to play with.
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
July 5, 2011 at 3:42 pm
I have experienced this exact issue and found that there wasn't one specific way to solve it, short of migrating to a 64-bit environment. After significant research the error itself comes down to SQL Server's internal memory pools. Backups fall under the MTL (MemToLeave) pool. Each database backed up will take about 4mb in this MTL pool. If you have 1 job that backs up all your user databases at once (vs 1 backup job per database) that is a cumulative amount of MTL memory used. For example, if you had 10 databases backing up in one backup job that would be 40mb of MTL Memory used.
I may be mistaken but I think the default MTL pool on a 32-bit system is 384mb. You can adjust this by adding a -g switch at startup (example -g512). In my case that wasn't helpful.
Now there are a few other things that can use the MTL pool and in your case is probably a cumulative effect. Other MTL users (but not limited to) CLR, Linked server or COM Objects that us sp_OACreate, extended SP's. Also very fragmented virtual memory in the non-buffer pool can also cause a 701 error, which I suspect you have in your SQL Log.
I know this from a support call I had with MS on the issue and in our case the largest single user of MTL memory was none of these, it was McAfee's Host Intrusion Prevention application. We determined that fact by running a query to look for any non MS applications. My opinion on the matter is "don't install McAfee HIP on a 32-bit system."
select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation'
To find anything that is running the sp_oa com request there is this query:
EXEC sp_MSforeachdb '
IF EXISTS (SELECT * FROM ?.dbo.syscomments WHERE CHARINDEX (''sp_OA'', [text])>0)
SELECT
LEFT (''?'', 30) AS db, LEFT (o.name, 40) AS sproc,
LEFT (REPLACE (REPLACE (SUBSTRING ([text], CHARINDEX (''SP_OA'', UPPER ([text])) - 10, 60), CHAR(10), ''''), CHAR(13), ''''), 60)
FROM ?.dbo.syscomments c
INNER JOIN ?.dbo.sysobjects o ON c.id = o.id
WHERE CHARINDEX (''sp_OA'', [text]) > 0'
I hope this is helpful to you, I spent 2 weeks troubleshooting this problem.
July 5, 2011 at 11:14 pm
Hi Mortalic,
Thanks for sharing your previous bug busting, i feel that our cases are similar, the unfortunate point is that upgrading to 64bit is not an option for me. I feel that the -g option will only delay the frequency of the memory issues. I've ran the script that you provided and nothing unusal was present.
Cheers,
Mr J 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
July 6, 2011 at 8:58 am
Yes 64-bit wasn't an option for me either, in fact I still have this DB server running in production. Anyway, the next question I would ask is, "How many databases you are backing up and how you accomplish that?"
(maintenance plan wizard, third party backup software, etc...)
Also I feel I should explain that in 32-bit edition, SQL Server can really only allocate something like 2gb of memory for itself and the MTL pool takes part of this. Adding a -gNNN startup param would scavenge memory away from SQL Server itself.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply