Long query block insert?

Hi,
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH TanHi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert data.
> Can you try using BCP OUT with -b (Batch size) option and see what happens.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > Hi,
> > Have a question, I have a select query that I used for bcp out one of my
> > MS
> > SQL 2000 DB table ( about 30 million of records) and it take more than 3
> > hrs
> > to finish(about 13 million record). It happen that when this bcp process
> > is
> > running no record can be inserted to this table.
> >
> > Isn't that the select will release lock after it read pass the data?
> >
> > Thanks!
> >
> > KH Tan
>
>|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird!
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
> > Hi,
> >
> > Generally for BCP OUT the table will not be locked and user can insert
data.
> > Can you try using BCP OUT with -b (Batch size) option and see what
happens.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> > news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > > Hi,
> > > Have a question, I have a select query that I used for bcp out one of
my
> > > MS
> > > SQL 2000 DB table ( about 30 million of records) and it take more than
3
> > > hrs
> > > to finish(about 13 million record). It happen that when this bcp
process
> > > is
> > > running no record can be inserted to this table.
> > >
> > > Isn't that the select will release lock after it read pass the data?
> > >
> > > Thanks!
> > >
> > > KH Tan
> >
> >
> >