sql server odbc: Hi, we have an application that is sending a large number of insert
statements (>5000) through ODBC to a SQL Server 2000 database. Apparently
this is causing the transaction log for that database to fill up which then
causes the web site that depends on that database to crash. We keep
increasing the size of the transaction log to accommodate the inserts, but it
keeps growing. It recently grew to 12 GB which seemed to cause other
problems, so we backed up and ran dbcc shrinkfile, etc. But we can't keep
doing that. Here are the errors we get in the log file:

"The log file for database 'specialneeds' is full. Back up the transaction
log for the database to free up some log space.."

"Error: 9002, Severity: 17, State: 6"

"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 9002.
A subsequent backup operation may be slower/larger than normal."

Any idea how we can keep the transaction log from filling up all the time?
We have "Autoshrink" selected in the database options.

Thanks,

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.

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

[quoted text, click to view]