Large Transacation Log even with Simple Recovery

Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
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=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!

>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>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=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>