Recovery Model to be used for a Data Flow Task

Hello there

I have a SSIS 2005 DTSX Package which has several Data flow tasks which directly dump data between tables from 2 different databases.

Suppose if my destination database is named, DestinationDB, what recovery model should I select for this database so that my ldf file size does not explode? I want to keep the ldf file as small in size as possible.

1. Currently I have used Simple Recovery model, and the ldf file size goes to around 80 GB. (Inserting around 200 million rows)

2. Will Bulk Logged model be a better option?

Also what does a SSIS 2005 Data flow task use internally? (Insert operation or some sort of Bulk Insert between DBs)

Please help.

The fast load option on the OLE DB destination performs a bulk load.|||

Thanks for the response.

Does the fast load option keep the ldf file size to a minimum?

|||This probably isn't the correct forum for you, but I believe you'll want "bulk-logged recovery" so as not to grow the LDF file. Or split your data into smaller chunks using the MaximumInsertCommitSize setting on the OLE DB Destination.

recovery model change log

Hi
Is there any log of changes in recovery model?
If not directly, is there any way to know whether a
recovery model has changed in the past?
Thanks,
RonenMaybe profiler security audit event (Audit object derived event or Audit
object event) might show this...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ronen" <anonymous@.discussions.microsoft.com> wrote in message
news:013d01c3d4e9$a259f9e0$a001280a@.phx.gbl...
> Hi
> Is there any log of changes in recovery model?
> If not directly, is there any way to know whether a
> recovery model has changed in the past?
> Thanks,
> Ronen