Professional Web Applications Themes

How to switch off transactions? - Microsoft SQL / MS SQL Server

I update a very large set of records. This is not critical operation and it takes a lot of time. Also transaction log grows very hudge. Is it possible to switch off transactions for this operation (or for any other stored procedure)? Nothing will heppen when some records will not be updated. Than, transaction log would be small and whole update would be much more time consuming. Przemo...

  1. #1

    Default How to switch off transactions?

    I update a very large set of records. This is not critical
    operation and it takes a lot of time. Also transaction log
    grows very hudge. Is it possible to switch off transactions for
    this operation (or for any other stored procedure)? Nothing
    will heppen when some records will not be updated. Than,
    transaction log would be small and whole update would be
    much more time consuming.

    Przemo
    Przemo Guest

  2. #2

    Default Re: How to switch off transactions?

    You can't turn off transactions. You can however do the updates in smaller
    batches and that will help to control the log file (you can do log backups
    in between if your not in simple mode) and it will most likely be faster as
    well.


    --

    Andrew J. Kelly
    SQL Server MVP


    "Przemo" <p.dutkiewiczdeltatrans.pl> wrote in message
    news:074b01c34314$efcef2f0$a501280aphx.gbl...
    > I update a very large set of records. This is not critical
    > operation and it takes a lot of time. Also transaction log
    > grows very hudge. Is it possible to switch off transactions for
    > this operation (or for any other stored procedure)? Nothing
    > will heppen when some records will not be updated. Than,
    > transaction log would be small and whole update would be
    > much more time consuming.
    >
    > Przemo

    Andrew J. Kelly Guest

  3. #3

    Default Re: How to switch off transactions?

    Thank for replay!
    That is as I thought. But what you mean saying log
    backups? Backup to a streamer?

    Przemo
    Przemo Guest

  4. #4

    Default Re: How to switch off transactions?

    The transaction log gets large because every transaction must have a
    corresponding entry written to the log file before it is written to the
    database. The only way you can remove these entries are to commit them and
    then have a checkpoint issued. If you are in full recovery mode you must
    issue a log backup to truncate the committed entries from the log file so
    you can reuse the space. If you don't issue a log backup these entries will
    never be removed and the log file will just keep getting larger. If your
    updating a lot of rows you can do them in a loop and issue a BACKUP LOG
    command every so many loops to ensure the checkpoints happen and the
    committed transactions can be truncated. If you do all the updates in one
    large transaction (or a single update statement) the log can not truncate
    anything in that space until you have finished the update and committed.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Przemo" <p.dutkiewiczdeltatrans.pl> wrote in message
    news:66fd01c3433c$035c3860$a401280aphx.gbl...
    > Thank for replay!
    > That is as I thought. But what you mean saying log
    > backups? Backup to a streamer?
    >
    > Przemo

    Andrew J. Kelly Guest

Similar Threads

  1. Transactions
    By Eric in forum ASP Components
    Replies: 0
    Last Post: February 20th, 02:42 PM
  2. Transactions and Locking
    By gbrownuk in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 6th, 10:05 PM
  3. Transactions in webservices
    By Lars Siden in forum ASP.NET Web Services
    Replies: 0
    Last Post: November 7th, 08:30 AM
  4. Transactions....HELP!
    By John Berry in forum Informix
    Replies: 0
    Last Post: October 16th, 06:54 PM
  5. Transactions - ADO/SQL or both?
    By London Developer in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 10:44 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139