RE: Transaction Log filling up

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

Thank you for your response. Yes, I have already read this article and all of
the articles attached to it. I already know how to shrink the log when it
gets larger and I know how to limit the size of the log so it doesn't grow
beyond what I specify. However, that's not the problem. The problem is that
if I limit the log size, it fills up very quickly and the web site that
relies on it crashes. However, if I enlarge the log file, it quickly grows to
take up the new space that I have allocated. It seems that no matter how much
space I give it, it eventually grows to the total size allocated and then
starts throwing errors. Is there anything that can be done besides manually
backing up and shrinking the log on a regular basis?

The application that's causing it to fill up is sending about 5,000 insert
statements at a time. The overall size of the database doesn't increase that
much because the inserts are going into a temp table which then replaces an
existing table. So it would seem to me that there should be a way to have the
log clear itself out after the inserts are done so that it has space for the
next set of inserts.

Or perhaps the way that we are doing the inserts needs to change. Through
ODBC, we are sending consecutive "insert into tablename values ()". Is there
something that we can add to these statements to cause the log to truncate?
It used to be that under SQL 7 there was an option called "Truncate Log on
Checkpoint" which I don't see as an option under SQL 2000. Thanks for your
help on this!

Masen

"Sophie Guo [MSFT]" wrote:

> Hello,
>
> You may refer to the following article:
>
> 873235 How to stop the transaction log of a SQL Server database from
> growing unexpectedly
> http://support.microsoft.com/?id=873235
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>
>
>
>
.

Relevant Pages

  • Re: reclaim space
    ... If your text data is small, here is my theory why shrink does not work: SQL ... Dropping Clustered index and recreating it won't help you, as SQL Server ... DBCC SHRINKDATABASE/SHRINKFILE. ...
    (microsoft.public.sqlserver.server)
  • RE: SSIS package fail from Job
    ... We have not received your response. ... I recommend that you use SQL Profiler to monitor ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.sqlserver.dts)
  • Re: WSS and Database Size
    ... To expand on what Dave said, SQL won't shrink databases by itself. ... eventually be used by the database anyway and using existing white space is ... sites have ALOT of pictures, but the pictures are 4-8 times larges ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Query about the LIKE statement
    ... Thanks for your quick response. ... I understand that this would be more on the realms of Dynamic SQL but was ... Vince ... >> to teacher Angel and James, ...
    (microsoft.public.sqlserver.programming)
  • Re: Tools with/for Express
    ... I had to start with SQL Server ... I then attempted to use Query Analyzer. ... No such luck with Enterprise Manager. ... I tried harder to get it started because your response told me I ...
    (microsoft.public.sqlserver.tools)