Hello,
I need to understand transaction logs. I have an SQL server/application.
Every once in a while the application will tell the user there is an error
and that the transaction log is full. It then recommends backing up the
transaction log.
We have the TXLOG set to autogrow by 10% up to a 350mb limit.
I really dont understand why or how to "back itup" . We use Computer
Associates Arcserve this the Open Files Agent and SQL Agent. We do a complete
backup each night. Prior to doing this backup, we shut down the
applications database. We do not shut SQl down though. So I dont know what
this does as far as me being able to do a good restore. I Do not understand
checkpoints. Doe the TXLOG get cleared or shrunken back down since we shut
the associated database down ??
thanks
Andrew J. Kelly - 26 Aug 2005 02:56 GMT
What Recovery mode are you in? If it is FULL and you aren't issuing LOG
backups you should consider setting the recovery mode to SIMPLE.
This might help:
http://www.support.microsoft.com/?id=110139

Signature
Andrew J. Kelly SQL MVP
> Hello,
>
[quoted text clipped - 18 lines]
>
> thanks
Dan DeCoursey - 26 Aug 2005 13:46 GMT
THe Recovery model is simple....how does this help ?
thanks
> What Recovery mode are you in? If it is FULL and you aren't issuing LOG
> backups you should consider setting the recovery mode to SIMPLE.
[quoted text clipped - 24 lines]
> >
> > thanks
Andrew J. Kelly - 27 Aug 2005 02:20 GMT
In Simple mode the transaction log will automatically truncate when it gets
70% full. That means that the space in the log file of any transactions
that are fully committed or rolled back can be reused again. That is as
long as there are no open transactions in the way. The log file gets used
in like a big circle. It starts filling up the log from the beginning of
the file and when it gets to the end it wraps around to the beginning again.
If the transactions at the beginning have been committed they will get
truncated (essentially flagged so they can be overwritten) when the log
reached 70% full. That way it can wrap around and reuse the space. If
there is a long running open transaction in there it can not reuse the space
and will attempt to grow the log file until it can safely wrap around again.
So if the application is starting a transaction and never committing it the
log file will fill up and you will get this error. Usually this happens
when there is an error and the app doesn't clean up after it self and issue
a rollback. Closing the connection will automatically roll back the
transactions that the connection may have had open. I don't know how large
your db is but 350MB is not that large for a log file. A simple reindexing
of a table close to that size can fill the log file up on it's own. Since
you have it at a fixed size it will error when full.

Signature
Andrew J. Kelly SQL MVP
> THe Recovery model is simple....how does this help ?
> thanks
[quoted text clipped - 31 lines]
>> >
>> > thanks