Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45

    Unanswered: Stopping transactions being logged

    Hi,

    I have searched google and have found no real answer to this....

    We are creating a database to support an application which is loading in data as part of the cutover from their current db to SQL Server.

    The datafile is around 56GB and when the data processing occurs, as its not just inserts, the transaction logs fills out to around 65GB, and probably more, just that we ran out of space. So question is, is there a workaround for stopping the logging of this processing at all. I have seen that bulk-logged mode may be used, but the option with TABLOCK must be used, and there is no way to get hands on code which performs this processing.

    Is there a way to turn off transaction loggin at any point?

    Thanks,
    Jim

  2. #2
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    jim
    One way in this scenario is to truncate the log and shrink datbase, and try inserting once again...

    what sql version you are using??
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    well. if the question is how do we turn off the log then the answer is
    alter database dbx set recovery simple

    this will not turn off the logging per se but will write all commited transactions to the db and overwrite them in the log. it is considered recycling data. so technically you will want to commit these transactions more closely than you might have been.

    understand however that you will have to back up the log and the entire database immediately afterwards as you now have no record of your transactions taking place.

    then put the db back the way it was
    alter database dbx set recovery full

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As suggested if you keep the database recovery model to SIMPLE ensure regular intervals of backups are performed.

    There is no chance of stopping logging of transactions to Tlog in SQL Server.

    Ensure there are no disk space issues where Tlog and data files are located.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't turn transaction logging off using SQL Server, but you can minimize logging if you are willing to accept the risk that it implies.

    There are two database settings that will help. One is setting the recovery mode to SIMPLE. Even though it isn't supposed to make a difference for SQL-2000 databases with SIMPLE recovery, I've still seen cases where explicitly setting the sp_dboption to truncate log on checkpoint will clear up obstinate problems.

    Another coding change that you can make that will help is to explicitly truncate the log when you reach a point that you can safely clear it.

    I don't recommend these as day-to-day options. They make sense when you are doing a huge load, but they are not for general use!

    -PatP

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    In any case make sure to maintain regular backups... don't play with luck.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Thanks very much guys,

    looks like I'll be using the simple option for the data insert. Then swap it over to Full once finished.

    We use TSM as our backup mgr, which does all sorts of fancy, full weekly, nightly diff, hourly tx backups, but without the database in production we have nothing.

    Cheers,
    Jim.

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    glad to help

Posting Permissions

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