Hello, I have created a DTS package for importing about 4 million rows of
data in to SQL2K database daily. The problem is that transaction log file
bloats to 10-15 GB during update/delete phase.

Lets say I have two tables "tblDTSTemp" and "tblData"

DTS Package first must empty "tblDTSTemp" by executing "DELETE FROM
tblDTSTemp". I'm sure there is a better way to flush data from tables (table
truncating?) but for example sake how do I delete rows and periodically
shrink transaction log (about every 10,000 rows)?
Same problem with update, my DTS package executes following query after
data has been loaded from text file in to tblDTSTemp "INSERT INTO tblDATA
([Hash], [Date], [Val1], [Val2]) VALUES (SELECT [Hash], [Date], [Val1],
[Val2] FROM tblDTSTemp WHERE [Hash] Not IN (SELECT [Hash] FROM tblDATA )"
The actual syntax is deferent but for simplicity sake I shortened it.

I appreciate any suggestions.

Deleting the rows you can do with TRUNCATE TABLE, if the table is not
referenced by foreign keys and there are no delete triggers that you want to
fire.

If you can't do that, you can split up the delete in batches along the lines
of the following example:
SET ROWCOUNT 10000
WHILE 1=1
BEGIN
DELETE FROM some_table
IF @@ROWCOUNT = 0 BREAK
END

For importing data, you can set the insert batch size in the DTS package on
the Options tab of the Properties of your Data Transform task. 1000 is a
good number.

And you have to set the recovery model of your database to simple while you
do all this, otherwise the log file will grow no matter what.

--
Jacco Schalkwijk
SQL Server MVP


"GrindKore" <IDontLike (AT) Spam (DOT) com> wrote