Log File Full -> Back up Transaction Log

Hi.
I get the following error message occassionally,
"The log file for database 'TSBASE' is full. Back up the transaction log for
the database to free up some log space."
I'll backup the transaction log, and the database immediately. But after a
while (a few weeks), the error comes out again.
Why is there such an error? It seems like no matter how large I assign the
size of the transaction log to be, I'll still get this error one day. How
occassionally do I need to backup the transaction log? Is backing up the
transaction log the only solution? Will it come to a day whereby the log fil
e
gets really so full that even backing up won't solve the problem?
Thank you in advance.You need to do a bit of reading on basic database technology & concepts.
A transaction log is a record of all change to a database. So, every
time you change a database in any way (changing, adding, deleting data,
building indexes, dropping tables, adding new users, etc., etc.) the
transaction log will grow. The transaction log is a *very *important
part of a production database server.
Typically DBAs backup their production logs very regularly. If you
don't and you have some kind of failure on your database server
necessitating recovery from a backup, then you're going to lose vast
amounts of data. For example, I backup the transaction logs for all my
production databases every 15 minutes (with a full backup nightly) so,
in theory, I lose at most 15 minutes worth of data in the case of a disk
failure or database corruption of some sort. That's a very common thing
to do and 15 minutes, while it suits my situation but may not be right
for others, is also quite a common log backup interval. Some backup
their logs more often (every 5 minutes or less). Others backup their
logs less frequently (once an hour, once a day, etc.)
Timing all depends on your overall backup strategy. You must have one
of these - this is important.
If you're not interested in keeping incremental changes to a database
and you simply want to do full backups periodically then you can
automatically truncate the transaction log on every checkpoint
(typically every 30-120 seconds). This will keep the log from growing
very large (only a couple minutes transactions will be recorded before
getting truncated). You do this in SQL Server by changing the *recovery
model* of the database to SIMPLE with the ALTER DATABASE statement as
follows:
ALTER DATABASE TSBASE set RECOVERY SIMPLE
However, this will invalidate the transaction log and you will not be
able to backup the transaction log while in SIMPLE recovery mode.
(Actually you can backup the transaction log while in this mode but the
backups will be useless for recovery purposes because the log on those
backups will be invalid.)
For more information on transaction logs you could start with SQL Books
Online:
http://msdn.microsoft.com/library/d...>
_02_6xwz.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
wrytat wrote:

>Hi.
>I get the following error message occassionally,
>"The log file for database 'TSBASE' is full. Back up the transaction log fo
r
>the database to free up some log space."
>I'll backup the transaction log, and the database immediately. But after a
>while (a few weeks), the error comes out again.
>Why is there such an error? It seems like no matter how large I assign the
>size of the transaction log to be, I'll still get this error one day. How
>occassionally do I need to backup the transaction log? Is backing up the
>transaction log the only solution? Will it come to a day whereby the log fi
le
>gets really so full that even backing up won't solve the problem?
>Thank you in advance.
>|||Thank you for the explanation. Does backing up the transaction log reduce th
e
size of the log file then?
"Mike Hodgson" wrote:

> You need to do a bit of reading on basic database technology & concepts.
> A transaction log is a record of all change to a database. So, every
> time you change a database in any way (changing, adding, deleting data,
> building indexes, dropping tables, adding new users, etc., etc.) the
> transaction log will grow. The transaction log is a *very *important
> part of a production database server.
> Typically DBAs backup their production logs very regularly. If you
> don't and you have some kind of failure on your database server
> necessitating recovery from a backup, then you're going to lose vast
> amounts of data. For example, I backup the transaction logs for all my
> production databases every 15 minutes (with a full backup nightly) so,
> in theory, I lose at most 15 minutes worth of data in the case of a disk
> failure or database corruption of some sort. That's a very common thing
> to do and 15 minutes, while it suits my situation but may not be right
> for others, is also quite a common log backup interval. Some backup
> their logs more often (every 5 minutes or less). Others backup their
> logs less frequently (once an hour, once a day, etc.)
> Timing all depends on your overall backup strategy. You must have one
> of these - this is important.
> If you're not interested in keeping incremental changes to a database
> and you simply want to do full backups periodically then you can
> automatically truncate the transaction log on every checkpoint
> (typically every 30-120 seconds). This will keep the log from growing
> very large (only a couple minutes transactions will be recorded before
> getting truncated). You do this in SQL Server by changing the *recovery
> model* of the database to SIMPLE with the ALTER DATABASE statement as
> follows:
> ALTER DATABASE TSBASE set RECOVERY SIMPLE
> However, this will invalidate the transaction log and you will not be
> able to backup the transaction log while in SIMPLE recovery mode.
> (Actually you can backup the transaction log while in this mode but the
> backups will be useless for recovery purposes because the log on those
> backups will be invalid.)
> For more information on transaction logs you could start with SQL Books
> Online:
> http://msdn.microsoft.com/library/d...
es_02_6xwz.asp
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> wrytat wrote:
>
>|||No. It just means the space can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
David Portas
SQL Server MVP
--|||Yes Sir, Its not advisable to use
dbcc shrinkfile
or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to
shrink the file ?!! M i right ?
"David Portas" wrote:

> No. It just means the space can be re-used. It's not a good idea to
> keep shrinking the transaction log because shrinking per se won't
> reduce the storage requirement and auto-growing it again is a very
> expensive operation. The way to control log file usage is to perform
> regular transaction log backups and allocate a fixed, sustainable size
> to the log.
> --
> David Portas
> SQL Server MVP
> --
>|||It's only running out of disk space because the original poster never
backs up the transaction log. If he just sets up a backup scheduled (or
sets the recovery mode to simple) then he shouldn't have any worries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hemant Goswami wrote:
[vbcol=seagreen]
>Yes Sir, Its not advisable to use
>dbcc shrinkfile
>or
>backup log 'dbname' with truncate_only -- Will truncate Log File
>but as if problem is running out of space then at this moment it need to
>shrink the file ?!! M i right ?
>"David Portas" wrote:
>
>|||I agree with Mike, that if you have a proper backup strategy, the problem sh
ould never occour. Ther are cases though where I feel it's ok to truncate th
e log file, and that's if there has been some unususal activity in the datab
ase. If you e.g. has merged 2 databases, that might have made the logfile gr
ow, but since this might be a "once in a lifetime" thing that most likely wo
n't happen again, it might be ok to tshrink the logfile back to a decent siz
e.
Regards
Steen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> skrev i en meddelelse new
s:eBnOiOZcFHA.3712@.TK2MSFTNGP12.phx.gbl...
It's only running out of disk space because the original poster never backs
up the transaction log. If he just sets up a backup scheduled (or sets the
recovery mode to simple) then he shouldn't have any worries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Hemant Goswami wrote:
Yes Sir, Its not advisable to use
dbcc shrinkfile
or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to
shrink the file ?!! M i right ?
"David Portas" wrote:
No. It just means the space can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
--
David Portas
SQL Server MVP
--