SQL server is geeting hanged after few transacatio

Hi,
Iam a systems administrator i got a problem in my office ,
iam having a system with 1gb ram and 200 gb of hard disk
i have sql serverr 2000 is installed in this machine and nearly 15 systems are accessing the server,but initially when the clients access the server using VB application the sql server is increasing its memory usage and in the course the memory usage by the server is keep on increasing and finally at one position the sql server uses all the memory of the machine and hence the application gets hanged and no response from the server. I have increased the virtual memory upto 3 gb but the time lag has increased but getting the same problem,

when i stop the sql server the memory used by the sql is released and the sytem is getting in its normal state.

please help me how to tackle this problem i have been working on this problem from the last 4 days....

thanking you

It seems the application needs a lot of data to process.

I have seen this before and a quick solution for me was to limit the maximum memory that the server can use such that there will always be some memory for OS.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

What are your server memory settings? My recommendation is to let SQL Server configure memory dynamically. Is this a stand alone SQL Box? Also check out processor in Sys Monitor, how is that performing? Also run a Profiler trace to see if the bottleneck is being caused by some process.

You indicated that you restart SQL Server when ever this happens. What you can do is find out the process that is using maximum memory and if you kill that process SQL server will release the memory. Also try to find out if this is a particulr query /stored procedure that is causing it. You can use SQL trace to do this. Increasing memory is also a option that you can consider.