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