October 22, 2003 at 3:13 pm
We are getting a lot of 1204 errors on SQL 2000 with sp3 installed:
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration".
I need help to find the reason.
October 22, 2003 at 7:00 pm
It looks that you need to expand the lock number, or set lock dynamically.
October 22, 2003 at 10:26 pm
is your code using temp tables ?
October 22, 2003 at 10:28 pm
Check the autogrowth setting for the Db as well
October 23, 2003 at 12:49 am
Yes michaelr777 we are using temp tables
October 23, 2003 at 7:28 am
restart SQL server, then install SP3.
Why temp tables? Thanks.
October 23, 2003 at 9:10 am
sp3 is installed, SQL 2000 ent. on windows 2000 adv. server, 8 CPUs and 8GB RAM (/3GB,/PAE,AWE enabled).
Temp tables are used to prepare data and satisfy key uniqueness. What is the alterative ?
October 23, 2003 at 9:33 am
what is result after running "sp_configure locks" in QA?
October 23, 2003 at 10:26 am
Hi KStefan
You need to look at a couple of things.
How much memory is available? How much virtual memory is Sql Server tring to use?
What is the traffic on the server like? Are there a lot of transactions going on when the error occurs?
If the error occurs during scheduled jobs can you change the schedule to space them out or run them at a different time?
The solution will depend on the answers to these questions.
Also, you could open a support question w/MS.
Richard L. Dawson
Sql Server Support Engineer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
October 23, 2003 at 11:23 pm
Locks were: config value 10000, run value 10000. Now I changed this to zero.
After the error I put the /3GB switch in the boot.ini file.
October 24, 2003 at 8:21 am
how's going?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy