question on commit

Hi
i have a question about what the COMMIT command actually does
According to my understanding i thought a commit is just like including a marker and it shouldnt take time to finish.
Checkpoint is actuallly the process which writes committed transaction to the disk so that should be the one taking time.
If this is true why does BCP in take a long time at the end when its commiting all rows copied from the file
Anywhere i can go to read about what commit actually does and what checkpoint does etc
SanjaySanjay
BOL says:
COMMIT TRANSACTION
Marks the end of a successful implicit or user-defined transaction. If
@.@.TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed
since the start of the transaction a permanent part of the database, frees
the resources held by the connection, and decrements @.@.TRANCOUNT to 0. If
@.@.TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @.@.TRANCOUNT
only by 1.
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||Hi Sanjay,
By default checkpoints happen automatically and very regularly on SQL
Server. How often a checkpoint happen is determined by the Recovery Interval
setting. If SQL Server determines that the estimate time to recover the
database by rolling forward the transactions in the transaction log is
longer than the recovery interval, it will issue a checkpoint so all the
changes are flushed to the data file and don't have to be rolled forward the
next time the database starts up.
What I think happens is that BCP makes so many changes that it automatically
triggers a checkpoint on commit.
--
Jacco Schalkwijk
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||If I am not mistaken, BCP can also defer all index fixups to once ( at the
end)... I think TABLOCK might be required for this, but i am not sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||I think what you are looking for is the batch size. If
you do not specify a batch size by using ther -b
parameter, the entire set of rows being inserted is
treated as one transaction and all row inserts are
commited and checkpointed at the same time.
This option is documented in the BOL.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies(remove).com
>--Original Message--
>Hi
>i have a question about what the COMMIT command actually
does
>According to my understanding i thought a commit is just
like including a marker and it shouldnt take time to
finish.
>Checkpoint is actuallly the process which writes
committed transaction to the disk so that should be the
one taking time.
>If this is true why does BCP in take a long time at the
end when its commiting all rows copied from the file
>Anywhere i can go to read about what commit actually
does and what checkpoint does etc
>Sanjay
>.
>