Post reply

Transaction Log

  • March 16, 2011 at 5:01 am

    #236989

    Can anyone give a reason why when marked as SIMPLE it does not automatically truncate the transaction log?

  • March 16, 2011 at 5:20 am

    #1299275

    Are you doing some sort of bulk insert or any large amount of data modification? We had to update 46 millions records in a database a while back and the log file filled up the drive, so we had to do it in batches with a CHECKPOINT in-between batches.

  • March 16, 2011 at 5:29 am

    #1299278

    I beleive we are. Would it be sensible to apply a job to issue a CHECKPOINT every so often?

  • March 16, 2011 at 5:40 am

    #1299283

    you could, but it would happen in another session, so it might make things worse. You would have to test it. I would rather you put it into the DML as you batch it out. Is that not possible?

  • March 16, 2011 at 5:41 am

    #1299284

    Please take a look through this http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2011 at 5:48 am

    #1299288

    aa.nichol (3/16/2011)

    Would it be sensible to apply a job to issue a CHECKPOINT every so often?

    Not in general. If you're doing a bulk operation, you may want to either add checkpoints at certain points if it's a script file, or run some manually/automatically during that, but you don't need to run manual checkpoints in general.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2011 at 5:49 am

    #1299289

    white shadow (3/16/2011)

    you could, but it would happen in another session, so it might make things worse.

    ???

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2011 at 5:51 am

    #1299291

    Thinking it might lock the t-log file and put a hold on the script.

  • March 16, 2011 at 5:54 am

    #1299292

    white shadow (3/16/2011)

    Thinking it might lock the t-log file and put a hold on the script.

    When the system-generated checkpoints run (in a system process) does that stop all operations in a database?

    Checkpoint's not going to lock the entire transaction log. it will slow things down due the additional IO load, but that's regardless of what process the checkpoint is running in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2011 at 9:24 am

    #1299441

    Is it safe to run CHECKPOINT anytime?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply