SQL Server Performance Forum – Threads Archive
insufficient memory
This is an intresting story.I have windows2000 and sql7 standard edition.
To my knowledge,windows2000 and sql7 standard edition support only 2 gb of ram.
Recently i took a dba assignment from a big insurance company,where in we have sql7 standard edition is running at windows2000 at 4gb of ram,as per the recomendation of a famous IT company,who has supplied the application.Now once in 2 days i am getting insufficient memory problem as a result my database goes into suspect mode,in the night after the scheduled jobs like reindexing,check db,backup and fragementation check in the night(scheduled job)
My question is 1 what are the chancess that the server goes in to insufficietn memory
2 can i prevent this by setting somthing in sql server
3 is the server is using 4gb of ram or it usses only 2gb can anybody tell me how to proceed??????????
hanks and regards,
thiruvengadam. s.thiruvengadam
It will use only 2gb because of limitation and In the server settings are u using dynamically configure sql memory or
use a fixed size memory if it is fixed change to dynamically configure
option. Having 4gb ram u can go for enterprise edition so that it solves ur problem. Rushendra
Are you running more than one job simultaneously on the system? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Thanks a lot Rushendra,still i want to know why this error,is it because of all the scheduled jobs like checkdb,fragemtnation check,reindexing and finally backing up database. Thanks and regards
thiruvengadam_seenu s.thiruvengadam
To the question of Gaurav,no not imultaneously,but one after the other.I think there is a memory leak,but how to find out?????????/ s.thiruvengadam
What about the service pack level are u running with sp4 if not apply and
see. Rushendra
Did you monitor the memory utilization on the box using performance monitor. Genreally, there are very few known cases of memory leak in SQL Server Applications. Are you using and custom based application (using SQL-DMO etc.) for these activities? The suggestion of using the latest service pack by rushendra is recommended. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
No. of factors thay may cause insufficent resources : Insufficient memory allocated or available to SQL Server will degrade performance. Data must be read from the disk continually rather than residing in the data cache. NT and Windows 2000 perform excessive paging by swapping data to and from the disk as the pages are needed. A slow disk I/O (disk reads and writes) will cause transaction throughput to degrade. An improperly configured number of users can cause the system to run slowly or restrict the amount of memory otherwise available to SQL Server. A process may be forcing another process to wait, thereby slowing down or stopping the blocking process. To monitor for a low-memory condition, start with the defined object counters using SYSMON Memory: Available Bytes & Pages/sec. To monitor the amount of memory being used by SQL Server, examine the following counters:
Process: Working Set
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB) Refer thru other tips listed on this website which will give you required information on using PERFMON/SYSMON. And also as stated take advantage of Enterprise Edition if you can afford. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>