many BULK INSERTs and error "cannot obtain a lock"...

Hi,
I still have problem with my script that perform a sequence of
many
data files with a BULK INSERT in a loop and rarely (about 10% of
times) fails with "cannot obtain a lock" (see the original post
below).
Since this happens also on a dedicate server without any other
activity
(both sql server and the O.S. are idle apart for the BULK INSERT
loop)
I start thinking the cause could be in the ODBC connection and cursor
that are
constantly re-used during the whole loop (the odbc connection is
in autocommit) and so
A friend of mine suggested me that I could try to enforce a checkpoint
could a ' cursor.execute("CHECKPOINT") ' statement issued before
any BULK INSERT
solve my issue?
TIA!
bye,
PiErre
on 2007-02-16 PiErre wrote:
> Subject: suggestion to avoid error "cannot obtain a lock"
> Hi,
> I have a script (actually the same python script
> that runs locally on several ms sql 2000sp4
> server - connecting via mxodbc)
> that every night tries to bulk insert about 20 files
> of various size (from less than 10 MB
> to more than 300 MB - about 900 MB in total )
> and sometimes (about 5% of the times)
> the importation fails (randomly on
> one or more of the servers) with the error like
> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
> The SQL Server cannot obtain a LOCK resource at this time...
>
> There is no other activity on the db server at the error time and
> so I cannot detect what is the cause of the resource lock.
> The db is normally configured with recovery-model set to simple
> and autoshrink enabled (not my decision
> and I must provide clear evidence to have
> that "standard" changed), but even after
> disable it the errors still appear.
>
> What can I do to detect the actual lock cause?
> More important: what can I do to avoid
> such errors? I heard something
> about to set a checkpoint after every bulk insert
> but I don't know how to do that...
>
> Thanks in advance for your help!
> bye,
> PiErreTry specifying TABLOCK on the BULK INSERT statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PiErre" <siggy2@.supereva.it> wrote in message
news:1178184835.364450.30740@.y80g2000hsf.googlegroups.com...
> Hi,
> I still have problem with my script that perform a sequence of
> many
> data files with a BULK INSERT in a loop and rarely (about 10% of
> times) fails with "cannot obtain a lock" (see the original post
> below).
> Since this happens also on a dedicate server without any other
> activity
> (both sql server and the O.S. are idle apart for the BULK INSERT
> loop)
> I start thinking the cause could be in the ODBC connection and cursor
> that are
> constantly re-used during the whole loop (the odbc connection is
> in autocommit) and so
> A friend of mine suggested me that I could try to enforce a checkpoint
> could a ' cursor.execute("CHECKPOINT") ' statement issued before
> any BULK INSERT
> solve my issue?
> TIA!
> bye,
> PiErre
> on 2007-02-16 PiErre wrote:
>> Subject: suggestion to avoid error "cannot obtain a lock"
>> Hi,
>> I have a script (actually the same python script
>> that runs locally on several ms sql 2000sp4
>> server - connecting via mxodbc)
>> that every night tries to bulk insert about 20 files
>> of various size (from less than 10 MB
>> to more than 300 MB - about 900 MB in total )
>> and sometimes (about 5% of the times)
>> the importation fails (randomly on
>> one or more of the servers) with the error like
>> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
>> The SQL Server cannot obtain a LOCK resource at this time...
>> There is no other activity on the db server at the error time and
>> so I cannot detect what is the cause of the resource lock.
>> The db is normally configured with recovery-model set to simple
>> and autoshrink enabled (not my decision
>> and I must provide clear evidence to have
>> that "standard" changed), but even after
>> disable it the errors still appear.
>> What can I do to detect the actual lock cause?
>> More important: what can I do to avoid
>> such errors? I heard something
>> about to set a checkpoint after every bulk insert
>> but I don't know how to do that...
>> Thanks in advance for your help!
>> bye,
>> PiErre
>|||On 3 Mag, 12:08, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> Try specifying TABLOCK on the BULK INSERT statement.
>
There are good chance that your hint was EXACTLY what I was looking
for...
Thanks a lot!
bye,
PiErre