Re: huge log file when inserting data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: donald (donald.huppe_at_bocenor.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 13:43:01 -0800

Thanks for the answer

Before the insert, the table is empty. And the log also.

After the insert (1060253 rows) the table is 394696 KB wide
(reserved)
Nothing else is running on the database. No trigger.

The insert is in a transaction and it is committed.

I tried 3 times and the log is always about 1.7 GIG.

thanks

Don

>-----Original Message-----
>The entire million rows will be sent to the log and the
statement must end
>(or the transaction must be committed if you're using a
transaction) before
>any of the space used to log the million row insert can
be released.
>
>How big (i.e., how many bytes wide) are the records you
are inserting? Are
>they about 1700 bytes wide?
>
>1 million rows X 1700 bytes/row = 1.7 billion bytes.
>
>Of course, there's some other overhead in logging, so a
million records a
>little smaller than 1700 bytes would also give you 1.7GB
of log usage.
>
>What else is going on while you're running your insert?
If your insert
>involves only, say, 700MB of data but other processes are
running that
>insert or update another 1GB of data during that time,
the amount of space
>required is added together.
>
>Are you inserting records to a table that involves a
trigger? Trigger
>actions that change the database must be logged, too, and
add to your log
>space requirements.
>
>
>"donald" <donald.huppe@bocenor.com> wrote in message
>news:16bc601c41752$15c9a470$a101280a@phx.gbl...
>>
>> >-----Original Message-----
>> >Maybe you can shrink the fil......
>> >.
>> >
>>
>> My question is :
>>
>> I would like to know if it's normal that the log become
>> this big.
>>
>> And why am i not able to shring it, except with the
detach
>> database solution
>
>
>.
>

Relevant Pages

  • Re: Auditing changes made to table design (syscolumns table)
    ... With out using profiler the only way I know of is to get this info from the ... Keep in mind I know the database and the tables I would ... I attempted to write a trigger to log when a change was made to the ... > certain criteria against every transaction. ...
    (microsoft.public.sqlserver.security)
  • Enable/Disable a database trigger when running a dbunit test
    ... The dabase id's in this database are generated by a trigger. ... the transaction is commited after enabling the trigger: ... data in the database is lost, the testdata is now in place. ...
    (comp.lang.java.help)
  • Re: Enable/Disable a database trigger when running a dbunit test
    ... The dabase id's in this database are generated by a trigger. ... that the transaction is commited after enabling the trigger: ... rollback transaction ...
    (comp.lang.java.help)
  • Re: Transaction log and trigger
    ... You would have to "recover" the database to re-create the trigger. ... and then apply transaction log which was backed up for the next hour(by this ...
    (microsoft.public.sqlserver.server)
  • Re: huge log file when inserting data
    ... (or the transaction must be committed if you're using a transaction) ... Are you inserting records to a table that involves a trigger? ... space requirements. ...
    (microsoft.public.sqlserver.server)