Hi,

I have a huge DTS packege running right now, it's
inserting 400 million rows into a table that have a
clustered index and a non-clustered index on it. It has
been runing for 54 hrs now and the tranlog has grown to
over 160 gig.

I bet there is a lot of inactive tran-log by now. Is it
possible to truncate the log right now and shrink it, when
this DTS package is still running?

will this DTS package create/use tran-log and data file in
tempdb? can I shrink tempdb while this DTS is still
running?

urgently need your advice before my server running out of
disk space! thanks a ton!!

JJ

JJ
No. It isn't.
Try backup your log file ( by the way you did not mention what is recovery
model of your server) if you have FULL or BULK LOGGED recovery model.
In the future try keep your transactions as small as possible ,also consider
to drop indexes on the table when you load so many rows ,it can be speed up
your insertion.



"JJ Wang" <anonymous (AT) discussions (DOT) microsoft.com> wrote

thank you, Uri. when you say 'no, it isn't', you mean it
is not possible to truncate the log and shrink it, when
the DTS package is still running?

my database is currently set to simple. I can not drop
the index and dts it is because otherwise I will have to
build the index once the dts is done and I think this will
be even slower. I think this is a price you will have to
pay either way.

thanks again for your help, and Merry Christmas!

JJ

Right



"JJ Wang" <anonymous (AT) discussions (DOT) microsoft.com> wrote

The fact you have set your DB to simple is only part of the solution in a
huge datapump. By default DTS will not COMMIT the rows until the very last
one (or ROLLBACK).

On the last config page for the datapump you can choose to change your fetch
and commit sizes. They become smaller batches and then the SQL Server can
pause for breath and shift the data to disk.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"JJ Wang" <anonymous (AT) discussions (DOT) microsoft.com> wrote