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.

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 (AT) discussions (DOT) microsoft.com> wrote

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.

Hi All..

But isn't it possible to start a dts package and as the first step use some
kind af statement to tell sql not to fill up
the log-file ? No need for this when transporting data to a dw .. error
handling will be tackled as part of the package.

Of course it could break down in the middle ... but then i would have my old
dw environment and can run it again..

If this isn't possible how can i shrink the log (backup log (database) with
no_log) AND reduce the file size to the hereafter used space.

\Michael Vardinghus

Allan Mitchell <allan (AT) no-spam (DOT) sqldts.com> wrote

You need to setup your destination to SIMPLE recovery model and insert in
batches. This way the log gets to breathe.

--
--

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 (AT) wmdata (DOT) dk> wrote

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 (AT) no-spam (DOT) sqldts.com> wrote

The transaction may have been committed but not yet flushed
to disk - the pages for these transactions are in the buffer
cache. That's why the log will still grow as it needs to
keep track of these transactions so that they can be rolled
forward, applied if something happens where you have a
system problem, crash the server, etc. When SQL Server comes
back online, it can read those transactions from the log and
apply them even though they weren't yet physically flushed
to disk.

-Sue

On Tue, 13 Apr 2004 19:45:26 +0200, "Michael Vardinghus"
<mivar (AT) wmdata (DOT) dk> wrote:

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 (AT) wmdata (DOT) dk> wrote

Thanks Su

Does this mean that the log file doesn't physically grow during execution of the package and supplying th
package with truncate log at the end will prevent it from doing so

\Michael V.

Ah

All in ... is that one step in the package or the whole package ..

\Michael V.

All in = at the task.

--
--

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 V" <anonymous (AT) discussions (DOT) microsoft.com> wrote