Professional Web Applications Themes

log - Microsoft SQL / MS SQL Server

I have a wicked SP, by wicked I mean several temp tables, lots of inserts, updates and deletes. I don't have time to rewrite this SP right now so I need to fix a problem it is causing. The problem is when it's run it s the log from 25 mb to 270 mb. What would the best way to keep it from logging all the changes? I'm thinking about changing the db recovery from full to simple then back again. Or Should I backup the log and shrink it after it runs? The SP is only run once at ...

  1. #1

    Default log

    I have a wicked SP, by wicked I mean several temp tables,
    lots of inserts, updates and deletes. I don't have time
    to rewrite this SP right now so I need to fix a problem it
    is causing. The problem is when it's run it s the log
    from 25 mb to 270 mb.
    What would the best way to keep it from logging all the
    changes?
    I'm thinking about changing the db recovery from full to
    simple then back again.
    Or
    Should I backup the log and shrink it after it runs?

    The SP is only run once at night.

    Anyone have any other ideas?

    Thanks

    -Hutch

    I apologize for the double post I accidentally posted to
    OLAP discussion by mistake
    hutch Guest

  2. #2

    Default Re: log

    Changing the recovery mode to simple won't do anything to stop the amount of
    data logged for insert, update and deletes. If you have a situation where
    you can use SELECT INTO instead of INSERT INTO it may help but it sounds
    like you just need to optimize what your doing overall. Make sure your not
    issuing several updates when you could use one and things like that. There
    are no magic tricks when it comes to database integrity and that is what the
    log is all about.

    --

    Andrew J. Kelly
    SQL Server MVP


    "hutch" <com> wrote in message
    news:001501c360ec$d7c18c40$gbl... 


    Andrew Guest

  3. #3

    Default Re: log

    Check with DUMP TRANSACTION DBNAME WITH NO_LOG

    sH

    "Wayne Snyder" <com> wrote in message
    news:phx.gbl... 
    you 
    >
    >[/ref]


    Shamim Guest

  4. #4

    Default Re: log

    Use tempdb as much as possible ( it does less logging.) otherwise like
    Andrew says make sure you only update a record once ( not multiple times),
    Perhaps adding a loop with multiple shorter transactions wouldhelp ( but you
    would need to be able to restart in the middle)...

    No short cuts...

    --
    Wayne Snyder, MCDBA, SQL Server MVP
    Computer Education Services Corporation (CESC), Charlotte, NC
    www.computeredservices.com
    (Please respond only to the newsgroups.)

    I support the Professional Association of SQL Server (PASS) and its
    community of SQL Server professionals.
    www.sqlpass.org


    "hutch" <com> wrote in message
    news:001501c360ec$d7c18c40$gbl... 


    Wayne Guest

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