Error message from server: The log file is full. Backup transactio

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack - 24 Mar 2008 22:12 GMT

Hi,
I am working on a Access Sql system where I need to put a new release for a
system. This system involves transactional replication. Most of new screens
and code relate to client side sql server and the main server. When I am
trying to test some functionality on the server (main) side I am getting the
above error. Right now the main database file is 1. GB while the transaction
file is 76 GB. The problem is I cannot backup this database on the server as
it tells the log file is full. So I am not sure how to handle this situation
without having the ability to have a backup. Any help is appreciated. Thanks

Reply to this Message

densial - 24 Mar 2008 22:20 GMT

> Hi,
> I am working on a Access Sql system where I need to put a new release for a
[quoted text clipped - 5 lines]
> it tells the log file is full. So I am not sure how to handle this situation
> without having the ability to have a backup. Any help is appreciated. Thanks

try using DBCC SHRINKFILE to shrink the log file in conjunction with
the BACKUP LOG command, use sp_helpfile to find the log files logical
name, something like the following

SP_HELPFILE

this will return the logical name of the log file

DBCC SHRINKFILE (<LogicalName>)

I believe this moves all the data to the start of the file

BACKUP LOG <dbname> WITH TRUNCATE_ONLY

this then does the actual shrink. you may need to repeat commands 2 a
3 a couple of times until the filesize stops shrinking.

google or BOL the commands to make sure I have them right, I don't
have a DB handy to check.

Reply to this Message

Tibor Karaszi - 25 Mar 2008 08:39 GMT

You should learn the basics about backup and restore and based on that set the recovery model for
your database properly. In your case, it seems you have full recover model but you don't perform log
backups. This means that the log is never emptied so the ldf file keeps growing and growing. You can
empty the log file by either setting recover model to simple or by doing:
BACKUP LOG dbname WITH NO_LOG

And then shrink the ldf file (keeping an eye on virtual log file layout, according to
http://www.karaszi.com/SQLServer/info_dont_shrink.asp), to a reasonable size.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Reply to this Message