Professional Web Applications Themes

Eliminate logging while doing Bulk Insert - Microsoft SQL / MS SQL Server

Hi, I have a stored procedure where in I process around 2 Million records in a table. During this process I do "Select Into", "Insert Into" operations on to temp tables. At the end of the Stored Proc execution the .ldf file of the database is inflating up to 15GB to 17 GB. I believe all my operations are logged into this file. Do I have any options to avoid logging? Note: I don't want to do "DBCC SHRINKDATABASE". Thanks in advance, -- RS Please Respond to News Group Only...

  1. #1

    Default Eliminate logging while doing Bulk Insert

    Hi,

    I have a stored procedure where in I process around 2 Million records in a
    table. During this process I do "Select Into", "Insert Into" operations on
    to temp tables. At the end of the Stored Proc execution the .ldf file of the
    database is inflating up to 15GB to 17 GB. I believe all my operations are
    logged into this file. Do I have any options to avoid logging?

    Note: I don't want to do "DBCC SHRINKDATABASE".

    Thanks in advance,
    --
    RS
    Please Respond to News Group Only


    RS Guest

  2. #2

    Default Re: Eliminate logging while doing Bulk Insert

    What are your db Options, select into /bulk copy and truncate log on check
    point.

    As far as I know, truncate log is not compatible w/transaction log backups, so
    it would need to be re-set after each batch process if you have transaction log
    backups being processed.

    HTH

    JeffP....

    "RS" <ramludd.hr.lucent.com> wrote in message
    news:#u8XE6uRDHA.384TK2MSFTNGP12.phx.gbl...
    > Hi,
    >
    > I have a stored procedure where in I process around 2 Million records in a
    > table. During this process I do "Select Into", "Insert Into" operations on
    > to temp tables. At the end of the Stored Proc execution the .ldf file of the
    > database is inflating up to 15GB to 17 GB. I believe all my operations are
    > logged into this file. Do I have any options to avoid logging?
    >
    > Note: I don't want to do "DBCC SHRINKDATABASE".
    >
    > Thanks in advance,
    > --
    > RS
    > Please Respond to News Group Only
    >
    >

    JDP@Work Guest

  3. #3

    Default Re: Eliminate logging while doing Bulk Insert

    I think your only options are to:
    - Break your insert into batches so each smaller batch comits so not filling
    the log
    - I think changing your recovery mode to "Simple" will also reduce the
    logging done for Select...Into's ; but obviously this is a big change to
    your overall.SQL architecture.

    "RS" <ramludd.hr.lucent.com> wrote in message
    news:#u8XE6uRDHA.384TK2MSFTNGP12.phx.gbl...
    > Hi,
    >
    > I have a stored procedure where in I process around 2 Million records in a
    > table. During this process I do "Select Into", "Insert Into" operations on
    > to temp tables. At the end of the Stored Proc execution the .ldf file of
    the
    > database is inflating up to 15GB to 17 GB. I believe all my operations are
    > logged into this file. Do I have any options to avoid logging?
    >
    > Note: I don't want to do "DBCC SHRINKDATABASE".
    >
    > Thanks in advance,
    > --
    > RS
    > Please Respond to News Group Only
    >
    >

    London Developer Guest

  4. #4

    Default Re: Eliminate logging while doing Bulk Insert

    You can more or less avoid it with the following procedure:

    - Backup log normally
    - Set recovery to bulk_logged
    - do imports
    - Backup database
    - Backup log with truncate only
    - Set recovery full

    Although this makes you vulnerable to data loss if something goes wrong
    between step 4 and 5.


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "RS" <ramludd.hr.lucent.com> wrote in message
    news:#u8XE6uRDHA.384TK2MSFTNGP12.phx.gbl...
    > Hi,
    >
    > I have a stored procedure where in I process around 2 Million records in a
    > table. During this process I do "Select Into", "Insert Into" operations on
    > to temp tables. At the end of the Stored Proc execution the .ldf file of
    the
    > database is inflating up to 15GB to 17 GB. I believe all my operations are
    > logged into this file. Do I have any options to avoid logging?
    >
    > Note: I don't want to do "DBCC SHRINKDATABASE".
    >
    > Thanks in advance,
    > --
    > RS
    > Please Respond to News Group Only
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. Question bulk emails
    By geoff0000 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: August 4th, 10:26 PM
  2. SQL Bulk Loader
    By acsdirect in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 24th, 05:12 PM
  3. bulk loading
    By John Miller in forum Photography
    Replies: 13
    Last Post: July 16th, 09:18 PM
  4. BULK INSERT exercises from exam 70-229 training kit don't work
    By gene in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:00 PM
  5. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 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