1

Environment:

Window server 2012R2: RAM = 4GB

Sql Server 2008R2: Min server memory = 0MB Max server memory = 2147483647MB

Background of problem:

I am managing database of asp.net application and recently application started to crash (when user tried to sign-in to the application; user see timeout exception on the browser).

In order to rectify the issue I set timeout =1000 in connection string of webconfig file and that resolved the issue.

When application was crashing I tried to turn on the activity monitor on Sql server instance but I coudn't start activity monitor and got following error:

enter image description here

My finding so far: enter image description here

I started my quest and troubleshooted and found that there is memory pressure but I need expert advice if there is any link of activity monitor not starting with memory pressure? I know error is from database as Sql server was busy at the time of crash. How I can further troubleshoot the problem?

Improve this question
7
  • I'm voting to close this question as off-topic because it contains a lot of images and not enough information that explains the context of the images in relation to where the errors occur. Please add more details to your question and explain what you are trying to achieve.
    – John K. N.
    Commented Aug 23, 2017 at 6:33
  • Both GUI and your application experience time-out errors. While you cannot change it in GUI you can change it in your application code. Just increase the timeout. And instead of GUI use T-SQL code that executed in SSMS has no timeout at all
    – sepupic
    Commented Aug 23, 2017 at 7:03
  • @sepupic I increased the timeout in webconfig file and it solved the problem sorry I forgot to add in question before now I re-written the question please review.
    – shaadi
    Commented Aug 23, 2017 at 7:56
  • >>>if there is any link of activity monitor not starting with memory pressure<<< Activity monitor as many others GUI windows has a timeout set that you cannot change. If the query that it sends does not return in 15 seconds(may be less, I didn't ever measure it), it just interrupt query execution. To find out WHAT it's waiting for use sys.dm_os_waiting_tasks while you are waiting for it to execute its query. Of course it can be memory pressure, with 4Gb only...Consider to limit your max memory for SQL Server unless it's already limited by Editiont (in case you use Express Edition)
    – sepupic
    Commented Aug 23, 2017 at 9:04
  • 1
    I suggest you lower your Max server memory = 2147483647MB so OS has enough memory to do server scoped activity.
    – SqlWorldWide
    Commented Aug 23, 2017 at 15:23

1 Answer 1

Reset to default
1

You're running out of RAM on the server. One of the comments refers to lowering your Max Server Memory setting. For an instance with only 4GB RAM (which is not really enough to run SQL Server), your Max Server Memory should be set to 2048MB at most, possibly even lower.

Improve this answer
9
  • 1
    I love that he has RESOURCE_MEMPHYSICAL_LOW highlighted.
    – Hannah Vernon ♦
    Commented Aug 23, 2017 at 17:54
  • I requested to add 2GB of more memory into Server. I am limiting Max memory to 2048MB as @Rabdolph advised how about Min memory? should I also set Min memory = 1024MB. So that Sql server don't release 1024MB to OS and keep performing internal tasks as usual.
    – shaadi
    Commented Aug 24, 2017 at 0:05
  • Minimum should be left as is. I’ve never changed that setting on a production SQL Server instance in my entire working career.
    – Randolph West
    Commented Aug 24, 2017 at 0:06
  • 1
    It’ll just make the queries run slightly slower and make more use of TempDB.
    – Randolph West
    Commented Aug 24, 2017 at 0:28
  • 1
    Try it and see. The fact is 4GB isn’t enough for what you’re trying to do.
    – Randolph West
    Commented Aug 24, 2017 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.