SQL server is geeting hanged after few transacatio | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
Check the SQL server error log and event viewer logs for any information.
And trace the H/w & SQL server counters with the help of PERFORMANCE MONITOR (SYSMON), where you can get more information on these counters on this website homepage. What are the memory settings on SQL server?
Any other applications sharing Machine resources at the same time? If possible increase the physical memory on the box in order to obtain performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
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.
Interim solution would be to add more physical memory to the box. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
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.
Rather than finding and killing a particular process regualrly, its better to fine tune the troubled one or exclude from the SQL Server in order to function as a dedicated box. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>