Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SSIS and Import/Export (2008)  DTS TO SSIS package - transaction log problem

Author  Topic  kdickie Starting Member 1 Post Posted - 2009-08-27 : 13:35:28 Hi Everyone, We migrated our jobs to SQL Server 2008 from 2000 with the DTS migration wizard. We are now in the process of testing our SSIS jobs on SQL Server 2008. Our transaction log has grown enormously for the same DTS/SSIS job were running in SQL Server 2008. One of the SQL 2000 DTS jobs running on the SQL 2000 server takes an average of 17 seconds to run and generates a 43 MB of transaction log, the newly converted job to SSIS and running on a SQL 2008 (64 bit) server takes about 2 minutes to run and generates a 77 MB transaction log. The record count for the tables in both SQL Server 2000 and 2008 is identical. One SQL Server 2000 database has 8 scheduled DTS jobs that run daily and it went from generating less than 500 MB of transaction logs to generating over 1.4 GB of transaction logs on SQL Server 2008. The table record counts are identical in SQL Server 2000 and 2008 databases. Is this expected behavior for the transaction log to grow in size for the same end result? I would expect SQL 2008 to be faster and generate the same amount of transaction logs. Has anyone experienced this behavior? Thanks in advance. kdickie