update transaction

  • Dear all,

    We are in the process of doing data conversion.

    Hence, we prepare a lot of update queries in a single .sql script file.

    When we run this script, the log file increase dramatically until

    it consumed all of the disk space.

    During this process, we make our DB recovery model to "simple",

    my question is:

    1.Why is the log file not reused although the recovery model is set to simple?

    2.What should we do for these update statements in order to avoid the same issue encountered again?

    Please advice.

    Thousand thanks.

  • can you try to divide the transaction into many small transactions

    Thanks

    John

  • Log space cannot be reused until a transaction is committed and a checkpoint run. If you have one large transaction, it prevents reuse of the log file.

    Setting to simple, do you take a full backup immediately afterward? Or before? If not, you risk losing a lot of potential data changes.

  • Isn't auto commit is a nature of sql server? (correct me if i am wrong)

    in my case, my script look like below:

    update.....

    update....

    update....

    ...

    ..

    ...

    After the first statement update completed,it is not considered committed transaction?

    If it is not, what can do I to avoid the log file from growing?

    Please kindly advice.

    Thanks a lot.

  • yes and no.

    If you had

    Begin Transaction

    ......

    update x

    update y

    update x

    ....

    This is one transaction. You can also have Implicit transactions turned on (http://msdn.microsoft.com/en-us/library/ms188317.aspx). If that's the case, then each statement starts a transaction and you need to issue a commit. I'd check on this.

    Have you traced this to be sure that there isn't one statement that is generating an inordinate or unexpected volume of changes?

  • Or perhaps your log is just not sized correctly?

  • hi,

    does it mean that if i rewrite the query as below,the log file will be reused?

    BEGIN TRANSACTION

    update a...

    GO

    update b...

    Go

    COMMIT TRANSACTION

    BEGIN TRANSACTION

    update c...

    GO

    update d...

    Go

    COMMIT TRANSACTION

    BEGIN TRANSACTION

    update e...

    GO

    update f...

    Go

    COMMIT TRANSACTION

    My log file is set to 50MB increment (unrestriced growth).

    Please advice.

  • Yes, the two updates would be one transaction, and the log could not clear between them, simple mode or not.

    You could also set a t-log backup in the middle of the set to clear the log if you don't want to go with simple mode.

    begin tran

    update a

    update b

    commit tran

    backup log mydb to disk = ...

    begin tran

    update a

    update b

    commit tran

    I would track log size as you run this, maybe running it manually and checking the sizes so you know roughly what size the log needs to be. I like keeping full mode for this reason. That way if I see log backups (or fulls) way out of whack, I can go check on what happened.

Viewing 8 posts - 1 through 7 (of 7 total)

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