1

Problem

We have a SQL Server instance (2008) running our software with what we consider a small number of users (about 30 concurrent connected to the server at any given time)

As a vendor, we're not SQL DBAs and only really offer a basic skill-set for these sorts of issues. Most clients have their own SQL DBA to fill this role for them, but sometimes we have to step in and assist if the client is without adequate support.

Although they do use this server for more than just SQL and that is an issue that has to be dealt with separately, we recently noticed that this client's sqlservr.exe process is using a lot more RAM than we expect to see for our software.

Background

First, let me start by mentioning that I understand the SQL Maximum Server Memory default is basically set to use everything a server has available. We changed that value to 10GB as we expect them to only need about 4-6GB at any given time considering their size and portions of the software they use.

Some bits of information that may or may not be relevant to my question:

  1. They have the databases for our software set to FULL recovery model and the logs have been growing and growing without maintenance for about 3 years.
  2. When we restart the SQL Services, the users come back into the software and for a period of about 24hrs, they use the amount of RAM we expect to see them use (about 4-6 of the allocated 10GB)
  3. We have been running Perfmon to compare some of the counter results with a period of time where they weren't having this issue, but haven't noticed any significant differences between the logs
  4. Checked event viewer for any obvious memory issues

Conclusion

My only guess at this time is that this has something to do with the enormous logs/recovery model, or some SQL setting that relates to caching. Maybe this is normal behavior that a SQL Expert would plan to see, but in our limited experience running our software in SQL Server, this is abnormal for an installation of this size.

So to summarize it all down to a single TLDR:

Does the SQL Server 2008 Recovery Model and Log Size impact memory usage for the sqlservr.exe process? If the answer is yes, is my description of our "problem" normal for SQL and we just haven't experienced it before? If the answer is no, what are some other things you would suggest investigating to solve my issue?

Improve this question
2
  • 3
    Whether it fixes the memory issue or not - please have them perform a full backup the database to truncate the log. If they're not backing up the database, they are in for a world of hurt if it gets corrupted. And if they do backup with some method that doesn't truncate the log, why aren't they using Simple recovery model? Commented May 4, 2015 at 21:55
  • 1
    Actually, at this point, you should have them set the database to simple recovery, shrink the log file, set the database back to full, back it up (full backup), and immediately implement transaction log backups so you won't end up in this situation again. Commented May 5, 2015 at 3:01

1 Answer 1

Reset to default
2

How big is this database? SQL Server likes to put the entire database in memory. It sounds, from your question, like it's a 200 MB MDF with a 250 GB LDF, or something horrifying like that.

Recovery model doesn't enter into it unless they're grossly neglecting the database, which they clearly are.

  • Set the database to simple recovery.
  • Shrink the log file to a sane size.
  • Set the database to full recovery.
  • Run a full backup.
  • Immediately schedule regular full and transaction log backups.

I personally would not waste any time looking at anything else until you implement basic database maintenance (although 10 GB strikes me as a stingy memory allocation for SQL Server).

Improve this answer
3
  • Thank you Katherine. I agree the 10GB seems like a lot, especially for such a small user-base of our software. Unfortunately, as we tried to decrease the max memory allocation we found things getting insanely slow. What seems odd (to my inexperienced eyes) is the way sqlservr.exe gradually creeps back up to 10GB from the more normal 4.5GB over a period of about 7 days. Commented May 5, 2015 at 13:16
  • You misread me: 10GB seems like nothing. Laughably, ludicrously small. Stingy. I usually work on a larger scale, though. Commented May 6, 2015 at 2:26
  • Yes I did misread you. I understood your usage of stingy to mean that SQL Server was unwilling to give up any of the 10GB it was allocated, thus being "stingy". My mistake. To put things in context, a similarly sized client running the same software could get away with a SQL Server install running only 4GB of RAM total on the box. Commented May 6, 2015 at 13:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.