Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

About once a week, my MSSQL server is timing out, or rather the machine runs out of RAM. This morning it reached 3.9GB of the available 4, with MSSQL taking up 2.5GB.

I'm concerned that i've not configured SQL to release memory as it should, so I ran sp_who2 while the timeouts were occuring to see what process were running.

If i could post the CSV datafile i would, however, there were 85 processes in total, mostly related to the Full Text service:

  1. FT Gatherer - About 35 of these running under the 'sa' account against the master database with status of either sleeping or background, many were dependant on other processes. Is that normal?

  2. MySite database - There were only 5 processes for the one active site/database and all were either sleeping or suspended - but their lastBatch dates were set to 1/12/2020. Is that normal?

The datbase is only about 20mb in size the traffic levels are very low, so i'm thinking of maybe limiting the amount of RAM SQL has access to (from unlimted to maybe 2GB).

Any thoughts / advise would be appreciated.

Mny thanks

Ben

share|improve this question

3 Answers 3

You should always configure SQL with an upper limit, otherwise it'll take all the available memory. With 4 Gigs of RAM and only a 20 Meg database setting the memory limit to 2 Gigs should be more than enough.

Are you using full text for anything?

share|improve this answer

Does the system event log indicate errors when this is happening?

Does the SQL log indicate deadlocks or any other possible reason for the timeout?

Are you sure it is an issue with SQL and not caused by something else running on the server (intentional or otherwise)?

share|improve this answer

Try this. Install the Debugging Tools For Windows. Launch the windbg.exe on the console and leave it running. You would need to select File > Kernel Debug > Local tab. On the next occurrence, enter the !vm command, that will provide you with a complete picture of all the memory usage on the server.

Are you on x86 and using the /3GB switch in the boot.ini? In this scenario on an x86 32-bit architecture, you are predisposed to additional pressure on kernel memory (paged/nonpaged pools, system PTE's and file system cache), as /3GB only leaves 1 GB for the OS.

On x86, when you run !vm, you should see something like this:

NonPagedPool Max:      65281 (    261124 Kb)
PagedPool Maximum:    134144 (    536576 Kb)

These numbers can vary as it is set dynamically at startup. When you have an occurrence, you can compare the usage to the max to determine if it is a kernel memory issue.

http://www.microsoft.com/whdc/DevTools/Debugging/default.mspx

share|improve this answer
    
Hi Greg - thanks fo the info, i'll give that a go and see what i find out. Its an x64 system so shows all 4gb available. –  user29000 Dec 20 '09 at 16:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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