February 9, 2009 at 7:21 am
Hi,
I have SQL 2005 Standard (9.0.3200) edition running on windows 2003 standard edition, so I am limited to 4GB physical memory.
I occasionally get 'The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time' errors so I thought I would try increasing the memory available to SQL by including the /3GB switch in the boot.ini. However SQL has never shown any inclination to use this extra memory and I still get the out of locks massages at times.
Has anyone else seen this behaviour? Will the /3Gb help anyway if memory used for locks is not part of the buffer pool?
---------------------------------------------------------------------
February 9, 2009 at 7:29 am
SQL Server lock resources have nothing to do with how much memory you have in the box.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 9, 2009 at 7:40 am
I'd have to disagree with that. The no of locks is a dynamic setting, if SQL requires more , it takes them, as each lock uses a bit of memory, the restricting factor to not being able to take out more locks is not having the memory to do so.
So, will the /3GB switch help and why am i running out of lock resources if SQL memory using is still sitting at 1.6GB?
---------------------------------------------------------------------
February 9, 2009 at 4:45 pm
Might be the rest of the memory is getting utlised by OS or other applications running on this server. I would suggest you to use /3GB switch.
MJ
February 10, 2009 at 3:30 am
MANU (2/9/2009)
Might be the rest of the memory is getting utlised by OS or other applications running on this server. I would suggest you to use /3GB switch.MJ
the /3GB switch is already set. SQL shows no inclination to use the extra memory though.
---------------------------------------------------------------------
February 11, 2009 at 6:50 am
Have you capped the SQL Server memory? If so, try different amounts--maybe 2816 or 3072. Did you stop / start SQL Server? (Don't remember if this setting is dynamic in 2005 or not.) Maybe something else is holding onto some of the memory, so you might try rebooting the server to free it up. Let us know if you see any difference.
February 11, 2009 at 10:30 am
Linda Johanning (2/11/2009)
Have you capped the SQL Server memory? If so, try different amounts--maybe 2816 or 3072. Did you stop / start SQL Server? (Don't remember if this setting is dynamic in 2005 or not.) Maybe something else is holding onto some of the memory, so you might try rebooting the server to free it up. Let us know if you see any difference.
Linda, I have sql memory allocated as dynamic with no caps, memory settings are dynamic in SQL 2005. Only thing we changed was the boot.ini. This got me thinking though that had the server been rebooted after the change (did it in november) and I can find no evidence it was though the sysadmin who did this is reliable. I'll get his confirmation on whether it has been rebooted since, if not we'll reboot it at the weekend!
I'll report back, meanwhile if anyone else has insights on how lock memory is allocated and used please post!
regards
george
---------------------------------------------------------------------
February 11, 2009 at 5:47 pm
February 12, 2009 at 3:22 am
MANU (2/11/2009)
You might have chked-->http://www.sqlservercentral.com/Forums/Topic584519-357-1.aspx#bm584538
MJ
exactly, which is why I want SQL to use the extra memory I made available to it. It looks as if the sysadmins did not reboot the box after adding the /3GB switch( which i missed at the time), so we will know after a reboot scheduled for the weekend.
---------------------------------------------------------------------
February 16, 2009 at 1:53 pm
well server was rebooted at weekend and lo and behold SQL is now happily using 2.5GB memory for the buffer pool, so that solves that one.
cheers for your time everyone.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply