SQL Server Performance Forum – Threads Archive

Insufficient memory available..

This is sequence of error in sQL log.
I do have snapshot backup trird party software running continuastly on that server.
Large number of dbs.
What shoudl i do?
1.Failed to reserve contiguous memory of Size= 1179648. 2.Buffer Distribution: Stolen=8371 Free=107339 Procedures=53
Inram=0 Dirty=8248 Kept=0
I/O=0, Latched=1222, Other=83375 3.Buffer Counts: Commited=208608 Target=208608 Hashed=92845
InternalReservation=510 ExternalReservation=0 Min Free=19 4.Procedure Cache: TotalProcs=23 TotalPages=53 InUsePages=7 5.Dynamic Memory Manager: Stolen=8424 OS Reserved=2336
OS Committed=4294945098
OS In Use=1386
Query Plan=46 Optimizer=120
General=6640
Utilities=930 Connection=2074 6.Global Memory Objects: Resource=5978 Locks=69
SQLCache=144 Replication=4
LockBytes=2 ServerGlobal=55
Xact=181 7.Query Memory Manager: Grants=0 Waiting=0 Maximum=147780 Available=147780 8.Error: 17803, Severity: 20, State: 12 9.Insufficient memory available..

Is this SQL7 or SQL2K what Service Pack, etc. This is generally due to a memory leak… there are several SQL Server bugs in older versions which can cause leakages, one such leak was when shrinking databases in SQL7, but there will be plenty more… I’d suggest restarting the box and then monitoring memory usage per process (using perfmon) and see which process has a steady climbing graph… Cheers
Twan

May check reference of this KBAhttp://support.microsoft.com/defaul…s/q282/2/29.ASP&NoWebContent=1&NoWebContent=1 for contigious memory issues. Also have you enabled AUTO-CLOSE & AUTo_SHRINK options for the database? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

it’s sql 2000 no sp.

Then get it fixed by applying the latest SP as referred always.
And check above options also. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

my server has virtual memory 2gb-4gb
space available 6,5 gb
what if i set max virtual memory to 6 gb
Also
"Reserve physical memory for sql server" is unticked
should i specify this?

Increasing the Virtual memory beyond recommended value has negative effects. I’ll try to find a refernece to the same. BTW, what is the physical memory of the server? Is it dedicated SQL Server Box? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

xp_mserv returns 1024 mb physical memory
It is dedicated server but we running thirt party software to create hot backups
for standby server (for high availability)

I will not recommend using more than 3072 MB of page file. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I would suggest and insist to test and apply the latest service pack to get rid of those errors which’re corrected so. Because I would suspect from the above table points pinned about memory. Moreover you’re using third party application for the backup which is using SNAPSHOT backup, is expected to consume memory resources. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

so actually i would rather try to decrease virtual memorey to 3 g than to increase to 4 g?
I can’t apply sp because company which suply the app will stop supporting it if our inviropment will defer from theirs and they do not have any sp installed.
Isn’t this wondefull?

Let me tell you this… In addition to the performance anf functional issues, your server is vulnerable to virus attacks not to mention Slammer attack. So butter convince your vendor on the same… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Well i am trying hard to do this … corrections
xp_msver returns physical memory 1024 mb
meanwhile total phisical memore has 2 gb
so based on that can i increase virtual memory to 6 gb?

I don’t understand Why there is a difference between two? What is the physical memory on box, min and max memory and set working size configuration for SQL Server? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

O!sorry physical memory 2 g
I little in stress here:ran query on different server!

That is really strange to hear from a third party which they don’t support when using with SPs.
Follow as Gaurav suggest in terms of VM settings. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I have seen instances of this kind of behavior by third party vendors. I think while negotiating with third parties, one should keep the condition of application being run on latest service pack as a condition. Now in present case, If there are vulnerabilities in NewDBA’s server, the team that has to face problem is NewDBA’s team and not third party…. This sucks.. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I would also ask you to show the management about that KBA referred above which affected your system and get vendor to understand better about SPs. I believe thats how they make money…..[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!] _________ Satya SKJ Moderator SQL-Server-Performance.Com

The sad truth is that we appled sp3a once …app stop working
so we roll it a back

I’m sure for the error point 1 & 8 is rectified under the hotfix and SPs.
Is there any chance to stop the SNAPSHOT backup third-party application and look for the results. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I know this problem started since we started to use this software. My question is if there is any chance to fix the problem without stopping backups. Should i restart the server after changing virtual memory setting?

THen better to avoid this application for backups and depend on the legendary SQL statements for backups. Or addup more memory to get it running in terms of escape from above errors (few). Yes you should restart the server to affect the new VM settings, OS itself will prompt after the change. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, you need to restart the Box after changing virtual memory settings. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

thank you

]]>