Transaction Log Error SQL 8.0. Was it my fautl????

[eighthman11]

Hi everyone:

We received a error message "Log File to Database is Full. Backup the
transaction log to free up space."

I have a Access 2000 application that calls a Stored Procedure that
inserts about 5000 records into a worktable on a SQL server 8.0
database table. After the user is finished with the work table a
stored procedure deletes just the records that he was using in the
work file (so I can't use Truncate Table). I have the work table
linked to an Access database so I can't use a temporary table on the
server.

This action of 5000 records being added and deleted to this workfile
can occur 7 or 8 times an hour. And for the last week I've been
testing the application so I have probably been doing this action 10
to 20 times an hours.

I do not currently use a COMMIT with the INSERT Stored Procedure or
the DELETE Stored Procedure.

Although I didn't think this was a lot of records, could it be that my
application caused this error. Is there a way to find out for sure if
my application caused this error and if there something I can do in my
application to keep the Transaction Log from filling up.

Thank you for taking the time to read my post and any help would be
appreciated.

[Gord]

On Apr 16, 10:12 am, "eighthman11" <[e-mail]:

Hi everyone:

We received a error message "Log File to Database is Full. Backup the
transaction log to free up space."

I have a Access 2000 application that calls a Stored Procedure that
inserts about 5000 records into a worktable on a SQL server 8.0
database table. After the user is finished with the work table a
stored procedure deletes just the records that he was using in the
work file (so I can't use Truncate Table). I have the work table
linked to an Access database so I can't use a temporary table on the
server.

This action of 5000 records being added and deleted to this workfile
can occur 7 or 8 times an hour. And for the last week I've been
testing the application so I have probably been doing this action 10
to 20 times an hours.

I do not currently use a COMMIT with the INSERT Stored Procedure or
the DELETE Stored Procedure.

Although I didn't think this was a lot of records, could it be that my
application caused this error. Is there a way to find out for sure if
my application caused this error and if there something I can do in my
application to keep the Transaction Log from filling up.

Thank you for taking the time to read my post and any help would be
appreciated.

This is really more of a SQL Server question than an Access question,
but anyway...

Check the SQL Server BOL (Books Online) for ways to prevent this from
happening. If memory serves, the easiest way to do avoid database
bloat is to...

- set the Recovery Model for the database to "Simple",

- enable the "Auto-Shrink" option for that database, and

- back up the database (including the transaction log) regularly

....but your situation may require a slightly different approach. If
you need more assistance, try posting to a SQL Server newsgroup.

[Tom van Stiphout]

On 16 Apr 2007 07:12:00 -0700, "eighthman11" <rd******@nooter.com>
wrote:

If this happens during development:
backup log <db_namewith no_log

If it happens during production, you are not backing up (db or
transaction log) often enough, or are not providing enough disk space
for the db.

-Tom.

>Hi everyone:

We received a error message "Log File to Database is Full. Backup the
transaction log to free up space."

I have a Access 2000 application that calls a Stored Procedure that
inserts about 5000 records into a worktable on a SQL server 8.0
database table. After the user is finished with the work table a
stored procedure deletes just the records that he was using in the
work file (so I can't use Truncate Table). I have the work table
linked to an Access database so I can't use a temporary table on the
server.

This action of 5000 records being added and deleted to this workfile
can occur 7 or 8 times an hour. And for the last week I've been
testing the application so I have probably been doing this action 10
to 20 times an hours.

I do not currently use a COMMIT with the INSERT Stored Procedure or
the DELETE Stored Procedure.

Although I didn't think this was a lot of records, could it be that my
application caused this error. Is there a way to find out for sure if
my application caused this error and if there something I can do in my
application to keep the Transaction Log from filling up.

Thank you for taking the time to read my post and any help would be
appreciated.