February 11, 2011 at 12:58 pm
For an hour this morning, users lost connectivity to a database on a server. This server has about 77 small databases on it used for web applications. The server has 3GB memory with 2.9GB allotted for SQL Server. What should I look for to troubleshoot this?
These errors generated for a few different spid's in the logs at the beginning of the downtime:Source...spid1s Message...AppDomain 45 (xxx.dbo[runtime].44) is marked for unload due to memory pressure.
Source...spid1s Message...AppDomain 45 (xxx.dbo[runtime].44) unloaded.
During the hr downtime, these messages appeared for different spid's.Source...spid143 Message...There is insufficient system memory to run this query.
Source...spid143 Message....Error: 701, Severity: 17, State: 123
February 11, 2011 at 3:38 pm
You really should leave a minimum of 2 GB of memory for the OS on any server so, you have most likely depleted the system memory.
I would start lobbying very strongly for more memory for that box. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2011 at 10:54 am
Thank you, David! I was thinking the same thing.
February 14, 2011 at 12:31 pm
if the OS and SQL is x64 you should enable lock pages in memory
February 14, 2011 at 12:34 pm
alen teplitsky (2/14/2011)
if the OS and SQL is x64 you should enable lock pages in memory
Alen - do you have any MS articles supporting that? Curious as I have not seen that being a hard line position from MS.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2011 at 12:40 pm
there was a blog posting on MSDN a few years ago by one of the devs. when we first went to x64 a few years ago we had the exact same issue and took a week to figure it out. after we enabled lock pages in memory it went away after a SQL restart
we had this issue on a server with 32GB RAM too
February 14, 2011 at 12:47 pm
Our OS and SQL server is 32 bit though
February 14, 2011 at 12:47 pm
I know that it can be a fix for some situations but I don't believe it is recommended to be applied universally from what I have read up to this point. That may have changed which is why I asked if you had any articles supporting that.
In this case though the OP stated they only had 3 GB on the box and 2.9 GB allocated for SQL Server. No need to really look further on this one. 😉
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2011 at 12:51 pm
aroatenberry (2/14/2011)
Our OS and SQL server is 32 bit though
i would run a trace then to see the queries casing this. i've seen people write code to scan hundreds of millions of rows and run for days and they think it's OK to do this.
February 15, 2011 at 7:38 am
alen teplitsky (2/14/2011)
if the OS and SQL is x64 you should enable lock pages in memory
Lock pages in memory would be about the worst thing you could do here - quite possibly leading to an unstable or completely unresponsive server.
There is too little RAM (my 3 year old laptop has more RAM than this server does).
sql server max memory for this box should be set at about 1GB, no more than 1.5GB for sure. Even that may not be enough. 3GB just doesn't cut it any longer, unless you have total database sizes that range in the tens or hundreds of megabytes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply