Re: dts package and t log

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 21:10:48 +0100

INSERTING and UPDATING is not necessarily committed straight away. By
Default DTS says "Only commit the rows after the last one has gone through".
You can change this behaviour on the last tab of the datapump. The rows not
committed are held in the T Log for consistency. If you commit in batches
then you may have a siruation where some early batches commit but hen one
fails so is not committed.

Basically the default behaviour of TSQL/DTS is "All in or All Out" 

-- 
-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Michael Vardinghus" <mivar@wmdata.dk> wrote in message
news:%23OltK6XIEHA.1412@TK2MSFTNGP12.phx.gbl...
> How is comitted to be understood ? When inserting and updating data is
> comitted immediately ... in a dts package with lots of inserts and
update's
> this would mean
> that there is no growth in the log file .. but there is. How come ?
>
> \Michael V.
>
>
> Allan Mitchell <allan@no-spam.sqldts.com> wrote in message
> news:ON#4SvkHEHA.3840@TK2MSFTNGP11.phx.gbl...
> > Your understanding of the T Log is slightly out then.
> >
> > The T Log must store all uncommitted data + any worktables.
> >
> > Simple mode will allow SQL Server to use the Lazy Writer (I think) and
> flush
> > COMMITTED transactions from the log at intervals
> >
> >
> > http://www.support.microsoft.com/?id=317375   Log File Grows too big
> > http://www.support.microsoft.com/?id=110139   Log file filling up
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp  Shrink File
> > http://www.support.microsoft.com/?id=315512      Considerations for
> Autogrow
> > and AutoShrink
> > http://www.support.microsoft.com/?id=256650   INF: How to Shrink the SQL
> > Server 7.0 Tran Log
> > http://www.support.microsoft.com/?id=272318   INF: Shrinking Log in SQL
> > Server 2000 with DBCC SHRINKFILE
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "sqlsurfer" <anonymous@discussions.microsoft.com> wrote in message
> > news:7A312FCB-725D-4EE6-A067-0E9B147DA856@microsoft.com...
> > > Why does the t-log appear to log the whole transaction of an import of
> > data into a table when the database is in simple recovery model.  I
> thought
> > it would only log the extents that were effected but it takes a t log
that
> > is as large as the file being imported e.g. a 10 gig file needs 10 gigs
> > worth of t-log space.
> > >
> > > I know I can solve it by setting the commit batch size option but it
is
> > messing with my fundamental understanding of the t-log.
> >
> >
>
>

Relevant Pages

  • Re: API in VB code is rolling back SQL import but wizard does not
    ... >>I have a DTS package in visual basic that was created via the DTS ... >>This doesn't happen in the DTS wizard, I get a partial load on the ... > Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • isolation levels
    ... I am a developer developing web applications using ASP.Net, PHP and SQL Server. ... I did connect to the sql server and opened query analyser and then selected Northwind db and wrote the batch below ... (I realise that this will commit in milliseconds, that is why I did not commit it-assuming I had a different query inside the transaction that it will take 15 seconds to commit) ...
    (microsoft.public.sqlserver.programming)
  • Re: Repeatable read. What is it exactly...
    ... SQL Server will hold locks on the data you have read ... can change change the data until you commit or rollback. ... SQL Server locks the data you read. ... > as transaction Isolation levels go. ...
    (microsoft.public.sqlserver.server)
  • Re: transaction with unknown nbr of commands
    ... into a temp table or table variable in the stored procedure code. ... the possibility that some rows would commit and others not. ... >> explicit transaction with a BEGIN TRAN statement, ... Does SQL Server 2000 have some capabilities that I should ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Multiple databases performance
    ... No, not at commit time, that would be too costly. ... >assumed that when a power failure occurs and SQL Server restarts,>it replays the whole log file. ... it will go to where the most recent checkpoint occurred and then first REDO from there and lastly possibly UNDO open transactions. ... If you have many databases, it would take longer for SQL Server ...
    (microsoft.public.sqlserver.server)