SQL 2005 - System.OutOfMemoryException 2

[fnpuff]

Specs: SQL 2005 (9.0.1399), Win 2003, 3GB Ram

We have 2 SQL Servers running 4 databases each, the servers are in different offices (sister company). Server_A goes down (IIS) about every 2 weeks with an error message of: Exception of type System.OutOfMemoryException was thrown.

Server_B of course never has a problem. (Note we have 60 + users in office A, and 40 + in office B)

I have been monitoring SQLServer:Memory Manager on both machines and Server A's Total Memory NEVER STOPS GROWING! When the Total Server Memory reaches the Target Server Memory the sites go down!?!?

Server A (online for 7 days)
Target Server Memory 2621440
Total Server Memory 1761792

Server B (online for 60 days)
Target Server Memory 2621440
Total Server Memory 524288

Any ideas, should I add more memory, is more memory the fix? Should I run a different test?

Any help would be great; I am tired of rebooting the server every 2 weeks at midnight. Thanks - Corey

 

[SQLBill]

Use Enterprise Manager, expand Management, then expand Current Activity. Click on Current Processes. Look at the number of user connections you have. Is it more than the number of actual users? If so, then users are openning more than one connection and that's causing memory problems. When you reboot, those connections are being killed. Then as the connections build up again, the problem re-appears.

Another thing that can cause this is if Windows is not terminating idle connections in a timely manner. So a user makes a connection, does stuff, logs off, but Windows doesn't terminate the connection. Now they log on again (second connection)...etc.

-SQLBill

Posting advice: FAQ481-4875

 

[fnpuff]

Hey SQLBill,

Using SQL Server Management Studio, I looked into the "Activity Monitor" and things look normal (low). From the Performance Monitor I looked at; SQLServer:General Statistics / User Connections = 16

Do you know how I can find out if Windows is not terminating idle connections?

Thanks - Corey

 

[mrdenny]

If memory continues to increase it sounds like something is not deallocating objects correctly.

You want want to start by applying SQL 2005 SP1 and the other hotfix that has been released. They fix a whole lot of issues.

For Server A why is the max memory higher than the total memory? Try setting it to about 512 Megs less than the total physical memory.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com