no blocking with bulk insert

I am running in QA:
Bulk insert tableA from 'C:\file.tab'
In another window ,
select * from tableA gives me no records .. I would assume some kind of
blocking while the bulk insert does it jobs. So what exactly does bulk
insert to ? The job is running and the tab file has 50 million records but
still on the second window i can query and in sp_lock all I can see for the
table is an IX tab lock and theres no blocking too. I am using SQL 2000
So whats going on ?In addition after like 15 mins, it started blocking.. So my question is what
was it doing till then.. Does it try to read the file first or something ?
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> I am running in QA:
> Bulk insert tableA from 'C:\file.tab'
> In another window ,
> select * from tableA gives me no records .. I would assume some kind of
> blocking while the bulk insert does it jobs. So what exactly does bulk
> insert to ? The job is running and the tab file has 50 million records but
> still on the second window i can query and in sp_lock all I can see for
the
> table is an IX tab lock and theres no blocking too. I am using SQL 2000
> So whats going on ?
>
>|||No your are experiencing the effect of lock escalation. When you start the
bulk insert with no batch size or a batch size of 0 (zero) the complete file
will be inserted as one batch, so the lock accumulate and at a certain point
in time escalate to a table lock.
In order to prevent this you can take two approaches:
1) Use a batch size of 2499 to prevent the lock escalation form happening
2) Before you start the bulk insert take a rowlock on an entry in the table,
I always do this by inserting a dummy record at the end of the table. Keep
the lock. In another session do the bulk insert, because there is a lock
present you can not escalate to a table lock.
I prefer option 1.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBzxW5kUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> In addition after like 15 mins, it started blocking.. So my question is
what
> was it doing till then.. Does it try to read the file first or something ?
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> > I am running in QA:
> >
> > Bulk insert tableA from 'C:\file.tab'
> >
> > In another window ,
> >
> > select * from tableA gives me no records .. I would assume some kind of
> > blocking while the bulk insert does it jobs. So what exactly does bulk
> > insert to ? The job is running and the tab file has 50 million records
but
> > still on the second window i can query and in sp_lock all I can see for
> the
> > table is an IX tab lock and theres no blocking too. I am using SQL 2000
> >
> > So whats going on ?
> >
> >
> >
>