lock error

[mkey]

Hi all,
When I tryed to bcp in I'm getting this error in dos prompt. Its seems at first it runs okay at the begining then it gives me a lock error:
"Server has run out of locks"
How can I over come this error?
Thankyou,
mkey

 

[tlbroadbent]

Which version of SQL Server are you running? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mkey]

sorry! Sql server 7!

 

[tlbroadbent]

Check the SQL FAQ on SQL 7 locks.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14255

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mkey]

HI,
When I checked the sp_configure my lock is set to:
name minimum maximum config_value run_value
locks 5000 2147483647 0 0

Is it advisable to change this? If so how?
Thank you for your help. I greatly appreciate it.
mkey

 

[tlbroadbent]

No. 0 indicates dynamic handling of locks by SQL Server. What is the memory situation? The FAQ indicates this error really means there is not enough memory to allocate all the locks needed. Is the memory dynamically allocated also?

I've not tried this under these circumstances but it may be helpful to use the -b option with the bcp utility. This option can be used to set a batch size. SQL will process the number of records in each batch and commit batch transactions rather than trying to treat the entire input file as one batch. For example, you would use -b 5000 to process 5000 records in each batch. Adjust this number to your need. You should have a good backup before using this option. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.