Results after creating table and database:
2014-11-19 09:42:22.460db_work1152014-11-19 09:41:46.597ROWSdb_work4096
2014-11-19 09:42:22.460db_work1152014-11-19 09:41:46.597LOGdb_work_log1024
Results after insert into:
2014-11-19 09:43:58.207db_work1152014-11-19 09:41:46.597ROWSdb_work19456
2014-11-19 09:43:58.207db_work1152014-11-19 09:41:46.597LOGdb_work_log76216
I then deleted the table and database, then recreated them. Ran the SQL again to get log size, same results as first results shown above.
I ran this same log size SQL multiple times while the data flow task was running and I did not see the db_work_log size grow, stayed at 1024.
Here are the results after the data flow:
2014-11-19 09:49:12.830db_work1152014-11-19 09:45:01.010ROWSdb_work17280
2014-11-19 09:49:12.830db_work1152014-11-19 09:45:01.010LOGdb_work_log1024
Just wanted to give some examples of what was done and the results.
My conclusion, based off what others have said and from Microsoft is this. If the table is empty with no indexes then using the data flow will not add to the log size,but the insert into will. But if the table has data or has indexes then some logging will occur, if there is a clustered index then full logging will occur.
I ran the data flow task again without clearing the data, log file did not grow, still 1024. I thought it would.
I added a non-clustered index, did not run any load, the log file grew to 3936.
Did not run clear, ran data flow again, log file size is still 3936.
Deleted index, log size 3936.
Added clustered index, log size now 4776.
Ran data flow again, data not cleared before, log size now 4776. I was expecting this to grow. I don't understand why this did not grow. Anyone have any ideas as to why it didn't? Based on what was in the link to Microsoft I was expecting ot to still grow. Would it not grow because I just kept adding the same data? I never changed the source table in any of the test.
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.