Professional Web Applications Themes

avoid writing to log file on insert - Microsoft SQL / MS SQL Server

Is there any way to NOT write to the Log File when doing an INSERT on a database setup as Simple? I am hoping there's some T-SQL syntax that can prevent the INSERT from being logged. I have a table I write to but I don't care if it's saved. All the inserts on the table cause the Log to grow sometimes considerably on some days. Related question: I'm assuming if using transactions (begin, committ, etc.) that you need a Log File. Or is this not true? In the above case, I need to be able to do transactions on ...

  1. #1

    Default avoid writing to log file on insert

    Is there any way to NOT write to the Log File when doing an INSERT on a
    database setup as Simple? I am hoping there's some T-SQL syntax that can
    prevent the INSERT from being logged.

    I have a table I write to but I don't care if it's saved. All the inserts
    on the table cause the Log to grow sometimes considerably on some days.

    Related question: I'm assuming if using transactions (begin, committ, etc.)
    that you need a Log File. Or is this not true? In the above case, I need
    to be able to do transactions on some tables, but I don't care about the Log
    File as far as doing backups, etc.


    Jed Ozone Guest

  2. #2

    Default Re: avoid writing to log file on insert

    Jed,

    First off you might want to check your system date as it seems to be off a
    few days. But to answer your question there is no way to turn off logging.
    Every thing is logged to some extent or another. Most are fully logged
    operations but you can achieve what is called a Minimally logged load under
    certain cirstances, but a straight insert is not one of them. Things
    like BCP, Bulk Insert and Select Into can be minimally logged if the
    conditions met under the "minimally logged load" section of BCP in
    BooksOnLine is met. If your doing lots of inserts then you should do them
    in batches and backup the log in between. If your in simple mode you can
    just truncate it or if you do it in batches it should truncate on it's own
    eventually. Don't wrap all the inserts in one big transaction or you can't
    do either.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Jed Ozone" <jed [email]ozone[/email]> wrote in message
    news:%23NWWkODQDHA.1720TK2MSFTNGP11.phx.gbl...
    > Is there any way to NOT write to the Log File when doing an INSERT on a
    > database setup as Simple? I am hoping there's some T-SQL syntax that can
    > prevent the INSERT from being logged.
    >
    > I have a table I write to but I don't care if it's saved. All the inserts
    > on the table cause the Log to grow sometimes considerably on some days.
    >
    > Related question: I'm assuming if using transactions (begin, committ,
    etc.)
    > that you need a Log File. Or is this not true? In the above case, I need
    > to be able to do transactions on some tables, but I don't care about the
    Log
    > File as far as doing backups, etc.
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: avoid writing to log file on insert

    Andrew, thanks for the info (and noticing my date was off!). Hopefully
    doing something like having Truncate Log on Checkpoint set on the database
    and then issuing the checkpoint command periodically will work. Doing
    something like every 1000 inserts I could then issue a Checkpoint command
    should work I think (unless I'm completely misunderstanding the usage of
    Truncate on Checkpoint and the Checkpoint command). Trying to keep the
    program functioning as is. I just want to reduce the log size if possible.

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:ebt0e8DQDHA.3664tk2msftngp13.phx.gbl...
    > Jed,
    >
    > First off you might want to check your system date as it seems to be off a
    > few days. But to answer your question there is no way to turn off
    logging.
    > Every thing is logged to some extent or another. Most are fully logged
    > operations but you can achieve what is called a Minimally logged load
    under
    > certain cirstances, but a straight insert is not one of them. Things
    > like BCP, Bulk Insert and Select Into can be minimally logged if the
    > conditions met under the "minimally logged load" section of BCP in
    > BooksOnLine is met. If your doing lots of inserts then you should do them
    > in batches and backup the log in between. If your in simple mode you can
    > just truncate it or if you do it in batches it should truncate on it's own
    > eventually. Don't wrap all the inserts in one big transaction or you
    can't
    > do either.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Jed Ozone" <jed [email]ozone[/email]> wrote in message
    > news:%23NWWkODQDHA.1720TK2MSFTNGP11.phx.gbl...
    > > Is there any way to NOT write to the Log File when doing an INSERT on a
    > > database setup as Simple? I am hoping there's some T-SQL syntax that
    can
    > > prevent the INSERT from being logged.
    > >
    > > I have a table I write to but I don't care if it's saved. All the
    inserts
    > > on the table cause the Log to grow sometimes considerably on some days.
    > >
    > > Related question: I'm assuming if using transactions (begin, committ,
    > etc.)
    > > that you need a Log File. Or is this not true? In the above case, I
    need
    > > to be able to do transactions on some tables, but I don't care about the
    > Log
    > > File as far as doing backups, etc.
    > >
    > >
    >
    >

    Jed Ozone Guest

Similar Threads

  1. Writing a text file to the file system
    By cwbp in forum ASP.NET Security
    Replies: 3
    Last Post: April 2nd, 10:52 AM
  2. Setting the file permissions of a file I'm writing to
    By Dan Anderson in forum PERL Beginners
    Replies: 1
    Last Post: December 10th, 04:13 AM
  3. avoid streaming into shockwave file for internet
    By Carlos Gonçalves in forum Macromedia Director Basics
    Replies: 0
    Last Post: November 30th, 02:19 AM
  4. Avoid Insert
    By Henry in forum Macromedia Dreamweaver
    Replies: 3
    Last Post: July 18th, 04:42 AM
  5. Replies: 1
    Last Post: July 4th, 12:02 PM

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