0

I am using the following pattern to update my SQL Server records using Linq to SQL:

    List<int> allIds;

    using (MyDataContext dc= new MyDataContext())
    {
        dc.CommandTimeout = 0;
        allIds = dc.MyTables.Select(x => x.Id).ToList();
    }

    Parallel.ForEach(allIds, x => ComputeAndSave(x));

and

    ComputeAndSave(int x, MyDataContext dc)
    {
       var myRecord= dc.MyTables.Select(x => x).Where(x => x.Id == id).FirstOrDefault();
       myRecord.Total = myRecord.Total + 1; //some modification(s)
       dc.SubmitChanges();
    }

I use the same pattern in multiple parts of my application to update all rows in some tables. And in all these cases, memory used by SQL Server slowly increases and increases and increases. The only way I find to release all that memory is to stop and restart the SQL Server Service. Why is there a memory leak here and how can it be fixed?

Many thanks!

1
  • 1
    SQl Server is designed to use all the memory available on the server, that is why it should not be on the same server as anything else. That said, you can limit the amount of memory SQl Server uses. Ask you admin to do that and see if it fixes your problem. Commented Jun 26, 2012 at 17:17

1 Answer 1

3

SQL Server is supposed to take all available memory. It is supposed to run on headless servers.

In SSMS, open the server properties and set a reasonable amount of max server memory.

Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, but running on headless servers has nothing to do with memory usage. I also realized that this memory ballooning is only happening when the DB is in full recovery mode.
@user277498, regarding "headless": I meant that it is ok to take all memory when there is nothing else on that box. Anyway, I don't think that running in full recovery mode can cause a memory leak. Did you set a max memory value? How did it turn out?
Thanks! Your suggestion worked. So I am marking this as the answer. However, SQL Server not releasing the memory that it is no longer using still looks to me like a memory leak. It may be by design, but it continues to perplex me.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.