none
Turn off Transaction Log?

    Question

  • We are getting ready to update our main production database. This will involve executing dozens of T-SQL scripts to import data (hundreds of millions of rows of data from dozens of tables) from our old database to our new. When we're doing this import, our new database will not be accessed by any other process or any users. Is there any way we can "turn off" SQL Server writing to the transaction log during this import? We won't need to do any restores. If the import fails, we'll simply restore a clean and empy copy of the DB and start again.

    Thanks - Randy
    Wednesday, March 25, 2009 8:18 PM

Answers

  • Everything gets logged.  You cannot turn off the log.

    You can set it to "Simple" which will limit the amount of data saved after the records are committed.

    Wednesday, March 25, 2009 9:18 PM
    Moderator
  • Randy -

    You can't "turn off" your transaction log, per se. This is an important feature of any relational database system and it is written to first before any data is written to your data files. This is to protect the database and maintain data integrity should something interrupt the flow of data or kill the server in the middle of work being done.

    You can get out of Full Recovery mode and choose Simple recovery model or bulk-logged recovery model (http://msdn.microsoft.com/en-us/library/ms189275.aspx) which means if you are performing certain operations they will be minimally logged (see: http://msdn.microsoft.com/en-us/library/ms191244.aspx).

    This will reduce the impact on your transaction log file growth, potentially depending on how you are performing your actions (see that second link to get more).

    Even still, if you do this all within one large batch or one large transaction you may still experience growth issues and you may want to consider working with a chunk of data at a time, say importing hundreds of thousands of rows at a time. Even if you aren't using BEGIN TRANSACTION, each of your SQL Statements are considered an implicit transaction and all of that work will be logged to some extent.

    Thanks! Mike Walsh www.straightpathsql.com/blog
    Wednesday, March 25, 2009 9:22 PM
  • You cannot "turn off" the transaction log. You can switch the recovery model to Simple or Bulk-Logged during the import. You also might want to drop some of your non-clustered indexes in the destination database. This will make the import go faster.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    Friday, March 27, 2009 6:41 PM
    Moderator

All replies

  • Everything gets logged.  You cannot turn off the log.

    You can set it to "Simple" which will limit the amount of data saved after the records are committed.

    Wednesday, March 25, 2009 9:18 PM
    Moderator
  • Randy -

    You can't "turn off" your transaction log, per se. This is an important feature of any relational database system and it is written to first before any data is written to your data files. This is to protect the database and maintain data integrity should something interrupt the flow of data or kill the server in the middle of work being done.

    You can get out of Full Recovery mode and choose Simple recovery model or bulk-logged recovery model (http://msdn.microsoft.com/en-us/library/ms189275.aspx) which means if you are performing certain operations they will be minimally logged (see: http://msdn.microsoft.com/en-us/library/ms191244.aspx).

    This will reduce the impact on your transaction log file growth, potentially depending on how you are performing your actions (see that second link to get more).

    Even still, if you do this all within one large batch or one large transaction you may still experience growth issues and you may want to consider working with a chunk of data at a time, say importing hundreds of thousands of rows at a time. Even if you aren't using BEGIN TRANSACTION, each of your SQL Statements are considered an implicit transaction and all of that work will be logged to some extent.

    Thanks! Mike Walsh www.straightpathsql.com/blog
    Wednesday, March 25, 2009 9:22 PM
  • You cannot "turn off" the transaction log. You can switch the recovery model to Simple or Bulk-Logged during the import. You also might want to drop some of your non-clustered indexes in the destination database. This will make the import go faster.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    Friday, March 27, 2009 6:41 PM
    Moderator
  • Hi,
    As said truely, you could have to switch to the simple / bulk-logged recovery model to minimize log writing. There is one other way you may want to schedule T-Log backup on regular interval (say 15 mins) and wrap all your T-SQLs in transactions and run those in smaller chunks so that your T-Log will remain in shape.

    Regards
    Hemantgiri S. Goswami | http://hemantgirisgoswami.blogspot.com/ http://www.surat-user-group.org/ -- Surat SQL Server User Group
    Saturday, March 28, 2009 12:20 PM
    Moderator
  • Hemantigiri -

    It sounds to me like he is already backing up his logs ever 15 minutes. Glad we agree on the smaller chunks and recover models though. :)
    Thanks! Mike Walsh www.straightpathsql.com/blog
    Saturday, March 28, 2009 12:41 PM