Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: "trunc. log on chkpt" help

    First time poster...
    Alright this is what I got...I am running MSSQL server 2000.
    I have already restored a copy of the production db to our test sql server.

    What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:

    alter table Table_Name add New_Column int
    GO
    EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
    GO
    update Table_Name set New_Column=1
    GO

    Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "

    I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.

    Or is there a way to make those changes using bcp?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
    When you are finished, set the recovery model back to "full" and run an immediate backup.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Posts
    13
    Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!

  4. #4
    Join Date
    Jan 2007
    Posts
    13
    I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!

Posting Permissions

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