SQL Server Memory continually increasing...

[eskap]

Our SQL7 box has been having a problem where the memory continually increases until it runs out and then dies. Is there a way to identify the source of all current requests against the database (applications/systems/users)?

Thanks.

 

[tlbroadbent]

In Enterprise Manager, select Management | Current Activity | Process Info.

In Query Anlyzer execute the system stored procedure sp_who. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[eskap]

Okay, this is perfect.. but is there any way to tell which user or application is causing the SQL memory to leak so quickly. It is basically being eaten away until it dies.

Thanks.

 

[kaveneydavis]

I take that you are not allowing ad-hoc sql to be run against your sql server. We had the same problem until i got the developers using stored procedures as each time they ran some ad-hoc sql it created a new query plan which uses memory. When we got them to use stored procedures the memory useage went down considerably. we now have plans that have been reused nearly 200000 times (some of the plans used to consume 30 to 40 kb of memory)

Mark