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.

"eighthman11" <rdshultz@nooter.com> wrote in message
news:1176732621.546534.270820@y80g2000hsf.googlegr oups.com...
> Hi everyone:
>
> We received a error message "Log File to Database is Full. Backup the
> transaction log to free up space."

My guess is either a) NO transaction log backups are being made, or your b)
your transaction log isn't being allowed to grow enough so that between
backups it can expand enough to handle your workload.

Simple fix is to fix either of the above two issues.


>
> 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.
>



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

eighthman11 (rdshultz@nooter.com) writes:
> We received a error message "Log File to Database is Full. Backup the
> transaction log to free up space."

I addition to Greg's post, ask yourself if you need up-to-the-point
recovery in case of a failure, or you are content with restoring the
last backup. In the latter case, say

ALTER DATABASE db SET RECOVERY SIMPLE

also run DBCC SHRINKFILE to reduve the log file to reasonble size.

Note: if you are not the DBA on the server, leave it to the DBA to do this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi

In addition to the other posts, have you looked to make sure you are not
leaving transactions open.
If this is the case, the transaction log will grow anyways.

M



"eighthman11" <rdshultz@nooter.com> wrote in message
news:1176732621.546534.270820@y80g2000hsf.googlegr oups.com...
> 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.
>