August 13, 2003 at 8:52 am
Guys,
While running a procedure I am getting following error :-
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS. [SQLSTATE HY000] (Error 1204). The step failed.
When job was failing my locks and User_connection value was 0 ( Which is unlimited), But I changed them Back to 50000 and 5000 respectively just for testing purpose.Still I am getting Error 1024. Any Idea?.
August 13, 2003 at 9:14 am
Could you post the procedure so we can take a look. There might be a recursive call somewhere that's not exiting?
August 13, 2003 at 9:20 am
Here is the procedure, Some time its runs fine and some its fails.I dont understand why?.
CREATE PROCEDURE [sp_lossrun_web_sub] AS
/** Submission Clearence **6/22/02**/
/** delete table info and add new data **1/7/01**/
/* Adding LA01_COMPANY for Project 1598 in order to make company name dynamic on lossrun.asp 1/21/03 SNC */
/** change delete to truncate table in order to decrease run time 3/04/03 SNC **/
TRUNCATE TABLE LC87_COVERAGE_D
TRUNCATE TABLE CRESERVE
TRUNCATE TABLE CCOMMON
TRUNCATE TABLE LC87_AGENT_D
TRUNCATE TABLE LD59_CAUSE_OF_LOSS
TRUNCATE TABLE NAME_ADDRESS
TRUNCATE TABLE PCOMMON
TRUNCATE TABLE tbl_sclear
TRUNCATE TABLE LA01_COMPANY
insert into db_iis..CRESERVE
Select * From OAKHILL.db_history.dbo.CRESERVE
insert into db_iis..LC87_COVERAGE_D
Select * From OAKHILL.db_history.dbo.LC87_COVERAGE_D
insert into db_iis..CCOMMON
Select * From OAKHILL.db_history.dbo.CCOMMON
insert into db_iis..LC87_AGENT_D
Select * From OAKHILL.db_history.dbo.LC87_AGENT_D
insert into db_iis..LD59_CAUSE_OF_LOSS
Select * From OAKHILL.db_history.dbo.LD59_CAUSE_OF_LOSS
insert into db_iis..NAME_ADDRESS
Select * From OAKHILL.db_history.dbo.NAME_ADDRESS
insert into db_iis..PCOMMON
Select * From OAKHILL.db_history.dbo.PCOMMON
insert into db_iis..tbl_sclear
Select [nme_busunit],[nme_insured], [adr_address], [adr_city],
[adr_st],[adr_zip],[cde_status],[dte_edate]
From OAKHILL.db_history.dbo.tbl_sclear
insert into db_iis..LA01_COMPANY
Select * from OAKHILL.db_history.dbo.LA01_COMPANY
GO
Thanks for your help.
August 13, 2003 at 9:29 am
When do you run such a procedure, and how often? There doesn't seem to anything particularly unusual about the procedure, unless the tables in question are enormous and the proc is being run, say, evey minute or something...
How much data do these tables possess, and have you been able to narrow down any patterns regarding when and possibly for whom, the procedure reports error 1024?
August 13, 2003 at 9:40 am
I schedule this proicedure to run 6 Am and there is another job on another machine which runs about 5 Am and completed around 5:15 and feed Data to this procedure. The frequency of the procedure is once in a day. I dont know why its gives me Locks and User error?. Some day its runs fine. Thanks fro your time.
August 13, 2003 at 11:03 am
Really not quite sure. Unless you can identify some sort of patterns as to when the job fails, I'm not sure why the proc would return that error, particularly if there's no one in the system...Hopefully someone else will have some insight...
August 13, 2003 at 11:04 am
BTW, what version of SQL Server are you running?
August 13, 2003 at 11:24 am
Its SQL Server 2000 on Window2000, Np. I really appreciate your help.Thanks for the info.
August 13, 2003 at 11:37 am
Books Online suggests putting the database into single-user mode for the during of the operation causing this error, if possible. Also, since you are using TRUNCATE TABLE, it is clear that you don't care about any transactional problems (since TRUNCATE is a non-logged operation.) Books Online makes a second suggestion to use bcp for bulk inserts, because it will not acquire locks on the tables as heavily as other operations, since it treats large operations as a single transaction...so investigate using bcp, ok?
[/quote]
August 13, 2003 at 11:38 am
Quoted from BOL.
"Error 1204
Severity Level 19
Message Text
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.
Explanation
This error occurs when there are not enough system locks to complete the current command. SQL Server then attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the lock limit is determined by the available memory. When statically configured, the lock limit is determined by the sp_configure setting.
If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient indexes to run your query efficiently, and that the transaction isolation level for your application is not more restrictive than necessary.
Action
Either execute the command again when activity on the server is low, or have the system administrator increase the number of locks by executing sp_configure from the master database.
To view the current configuration:
sp_configure locks
GO
This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:
sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO
Stop and restart Microsoft® SQL Server™ so the changes can take effect. Locks are allocated at system startup.
If the number of locks cannot be increased at the current time, and the single action requires more locks than the server is currently configured for, you may be able to reduce the number of locks required for the operation. For example, try the following:
For large UPDATE statements, break the updates into smaller units that will affect only a subset of records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 9999
GO
to several UPDATE statements:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 4999
GO
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 5000 AND 9999
GO
For a maintenance type of task or for a global update, consider putting the database into single-user mode (if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
For a large bulk copy operation, the entire operation is treated as a single transaction. When you use the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of rows specified. At the end of each small transaction, the system resources held by that transaction are freed, so fewer locks are needed. "
August 14, 2003 at 8:26 am
You could also try using the TABLOCK hint on the inserts and if the source tables are not being updated at the time, the NOLOCK hint could reduce the number of share locks. For example:
insert into db_iis..CRESERVE with (TABLOCK)
Select * From OAKHILL.db_history.dbo.CRESERVE with (NOLOCK)
August 14, 2003 at 9:23 am
Thanks you Guys for your valueable input. I re create all the Indexes and and increase number of lock to the database its seems to be working now. I really appreciate you guys help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply