"Snake" wrote:

> I had to kill a SS2008 bcp-in because of space issues with the log file
> device.  I believe the bcp had actually loaded the rows but had moved-on to
> creating the Clustered index. The flat-file had 147,000,000 rows @35 gig. It
> is my understanding that bcp loads the database pages and writes them to disk
> very quickly and logs the space allocations in the db log file.  What I don't
> understand is why it takes so long to roll-back (hours) when all it should be
> doing is putting the allocated extents back into the free pool and leave the
> data in-place as trash.  Obviously I am wrong or the rollback would not take
> so long.  The log file expanded to 49 gig before I killed the transaction. 
> Obviously more than space allocation was being logged.  Perhaps it was the
> attempt to create the Clustered Index and because of that the rollback is
> lengthy. 
>
> I would like to hear from someone who really understands what is going on.
>
> Thanks,
>
> Michael

"Linchi Shea" wrote:

> Well, there is a difference between fast bcp and 'slow' bcp in that not all
> bcp will result in minimally-logged operations. If you have data and indexes
> in a table, most likely your bcp will end up being a row-by-row operation and
> will be logged as such.
>
> BOL has more info on the prerequisites for minimal logging in bulk import:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bd1dac6b-6ef8-4735-ad4e-67bb42dc4f66.htm
>
> Linchi
>
> "Snake" wrote:
>
> > I had to kill a SS2008 bcp-in because of space issues with the log file
> > device.  I believe the bcp had actually loaded the rows but had moved-on to
> > creating the Clustered index. The flat-file had 147,000,000 rows @35 gig. It
> > is my understanding that bcp loads the database pages and writes them to disk
> > very quickly and logs the space allocations in the db log file.  What I don't
> > understand is why it takes so long to roll-back (hours) when all it should be
> > doing is putting the allocated extents back into the free pool and leave the
> > data in-place as trash.  Obviously I am wrong or the rollback would not take
> > so long.  The log file expanded to 49 gig before I killed the transaction. 
> > Obviously more than space allocation was being logged.  Perhaps it was the
> > attempt to create the Clustered Index and because of that the rollback is
> > lengthy. 
> >
> > I would like to hear from someone who really understands what is going on.
> >
> > Thanks,
> >
> > Michael

"Linchi Shea" wrote:

> Also, check out this whitepaper:
>
> http://msdn.microsoft.com/en-us/library/dd425070.aspx
>
> It has more info on minimal logging.
>
> Linchi
>
> "Linchi Shea" wrote:
>
> > Well, there is a difference between fast bcp and 'slow' bcp in that not all
> > bcp will result in minimally-logged operations. If you have data and indexes
> > in a table, most likely your bcp will end up being a row-by-row operation and
> > will be logged as such.
> >
> > BOL has more info on the prerequisites for minimal logging in bulk import:
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bd1dac6b-6ef8-4735-ad4e-67bb42dc4f66.htm
> >
> > Linchi
> >
> > "Snake" wrote:
> >
> > > I had to kill a SS2008 bcp-in because of space issues with the log file
> > > device.  I believe the bcp had actually loaded the rows but had moved-on to
> > > creating the Clustered index. The flat-file had 147,000,000 rows @35 gig. It
> > > is my understanding that bcp loads the database pages and writes them to disk
> > > very quickly and logs the space allocations in the db log file.  What I don't
> > > understand is why it takes so long to roll-back (hours) when all it should be
> > > doing is putting the allocated extents back into the free pool and leave the
> > > data in-place as trash.  Obviously I am wrong or the rollback would not take
> > > so long.  The log file expanded to 49 gig before I killed the transaction. 
> > > Obviously more than space allocation was being logged.  Perhaps it was the
> > > attempt to create the Clustered Index and because of that the rollback is
> > > lengthy. 
> > >
> > > I would like to hear from someone who really understands what is going on.
> > >
> > > Thanks,
> > >
> > > Michael