Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009

    Unanswered: DTS TO SSIS package - transaction log problem

    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.

  2. #2
    Join Date
    Jun 2009
    CA, USA
    Is recovery model of the database is same? in both versions?
    SQL Server MVP

  3. #3
    Join Date
    Sep 2009
    Dallas, TX
    Quote Originally Posted by MohammedU
    Is recovery model of the database is same? in both versions?
    That's likely the issue. Have a script set it to SIMPLE or BULK_LOGGED (with appropriate backups in place before and after) to minimize this issue.

    This talks about all the steps you need to do bulk imports: Create a SQL Server Job to Perform a Bulk Import | LearnItFirst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts