Log File is Full error: How to prevent this happening?

"The log file for database is full. Back up the transaction log for
the database to free up some log space."

Now I only know this way to deal with that manually,

Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.

But by this way, I could get same problem again, the log file is fill,
and need free up.

Could you give an idea how to prevent this from happening? what and
how should I do?

Thanks a lot in advance for your help.(danceli@.gmail.com) writes:

Quote:

Originally Posted by

"The log file for database is full. Back up the transaction log for
the database to free up some log space."
>
Now I only know this way to deal with that manually,
>
Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.
>
But by this way, I could get same problem again, the log file is fill,
and need free up.
>
Could you give an idea how to prevent this from happening? what and
how should I do?

First: decide what recovery mechanism do you need. Second: implement it.

Do you need up-to-the-point recovery? That is, if the database goes
belly-up, or somewhat makes a fatal mistake, are you content with
restoring the latest backup? Or do you want recovery as close to
the accident as possible. If you are content with the latest backup,
go for simple, and all you need to make sure is that you back up the
database regularly.

If you want up-to-the-point-recovery, the you need FULL recovery mode.
You also need to backup your transaction log regularly. How often
depends on the transaction rate in your database. If you never backup
the transaction log, it will outgrow the disk eventually. Note also that
by switching to simple recovery, you break the log chain, and the
ability to up-to-the-point recovery.

Also with full recovery, you need to backup your database regularly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||danc...
This can also be an application issue, I often encounter situations
where delevelopers do not batch their transactions, and thus do not
give the log a chance to truncate, if this is an inhouse app; updates
and deletes should be done in batches. The size of the batch should be
set by the ideal size of your transaction log, the size of the db
etc.. Also, be sure to dump the log periodically, typically I dump the
log every 15 minutes (when the db is in full recovery mode). This will
also assist in controlling the size of the log.
Cheers,
G|||GZ (gerald.zimmerman@.gmail.com) writes:

Quote:

Originally Posted by

This can also be an application issue, I often encounter situations
where delevelopers do not batch their transactions, and thus do not
give the log a chance to truncate, if this is an inhouse app; updates
and deletes should be done in batches. The size of the batch should be
set by the ideal size of your transaction log, the size of the db
etc.. Also, be sure to dump the log periodically, typically I dump the
log every 15 minutes (when the db is in full recovery mode). This will
also assist in controlling the size of the log.

I assume that you are thinking of is the case where you update, insert
or delete millions of rows in one go, and that in such case it may be
better to break up the operation in batches of say 500000 rows. Yes,
this can be a good idea. But it has to implemented in such a way that
if the operation fails half-way, the database is still consistent.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

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 file
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/de...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* 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 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 file
>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 the
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/de...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* http://www.mallesons.com
>
> 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 should never occour. Ther are cases though where I feel it's ok to truncate the log file, and that's if there has been some unususal activity in the database. If you e.g. has merged 2 databases, that might have made the logfile grow, but since this might be a "once in a lifetime" thing that most likely won't happen again, it might be ok to tshrink the logfile back to a decent size.
Regards
Steen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> skrev i en meddelelse news: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

log file full

The log file for database 'dbname' is full. Back up the transaction log for
the database to free up some log space.
How can i free up the space?Win
BACKUP LOG and then DBCC SHRINKFILE command.For more details please refer to
the BOL.
"Win" <aaa@.aaa.com> wrote in message
news:eu4XxnOaGHA.4916@.TK2MSFTNGP04.phx.gbl...
> The log file for database 'dbname' is full. Back up the transaction log
> for
> the database to free up some log space.
> How can i free up the space?
>|||Win (aaa@.aaa.com) writes:
> The log file for database 'dbname' is full. Back up the transaction log
> for the database to free up some log space.
> How can i free up the space?
You can delete other files so that the log can grow, or you can backup
the transaction log with BACKUP LOG.
But you should also ask yourself how you ended up in this situation. Did
you perform some wild update operation? Or did it come out of the blue?
What I'm getting is that when you run with full recovery, you need to backup
the transaction log, or else it will just keep growing. Backing up the
database does not truncate the log.
But do you need full recovery? Full recovery is needed if you want to be
able to do in point-in-time restore in case of a disaster. But if you are
content to restore the most recent full backup in that situation, you can
get along with simple recovery. With simple recovery, the log is truncated
after each committed transaction, more or less. To set the database in
simple recovery, do ALTER DATABASE db SET RECOVERY SIMPLE. Once you have
done this, you may want to reclaim some disk space with DBCC SHRINKFILE.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you might also want to check the auto-growth feature of the log-file...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ONR87pPaGHA.4564@.TK2MSFTNGP03.phx.gbl...
> Win
> BACKUP LOG and then DBCC SHRINKFILE command.For more details please refer
> to the BOL.
>
>
> "Win" <aaa@.aaa.com> wrote in message
> news:eu4XxnOaGHA.4916@.TK2MSFTNGP04.phx.gbl...
>

Log Error

Hi I am getting the follow error
'log file for database dbname is full, please backup the transaction log for
the database to free up some space.'
running backup log dbname with truncate_only sorts it out. All's I want from
my back up is to take a copy of some small databases every night. What I am
doing wrong to cause the log files to continue growing. I'm a designer not
an administrator so it is a little out of my area!Hi Chris
You may have a fixed size allocated to the database and in some
circumstances this may not be sufficient.
The may help you understand the log file architecture:
_876t.asp?frame=true" target="_blank">http://msdn.microsoft.com/library/d...asp?frame=true
and the whole chapter is a good read!
7v1h.asp?frame=true" target="_blank">http://msdn.microsoft.com/library/d...asp?frame=true
This may also helps:
_1uzr.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:O9744SeXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi I am getting the follow error
> 'log file for database dbname is full, please backup the transaction log
> for the database to free up some space.'
> running backup log dbname with truncate_only sorts it out. All's I want
> from my back up is to take a copy of some small databases every night.
> What I am doing wrong to cause the log files to continue growing. I'm a
> designer not an administrator so it is a little out of my area!
>|||To add to John's response, you can set your database recovery model to
SIMPLE so that committed data are automatically removed from the log. The
SIMPLE recovery model is appropriate when your recovery plan is to restore
from your last full backup and it is acceptable to lose data modifications
made since the backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:O9744SeXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi I am getting the follow error
> 'log file for database dbname is full, please backup the transaction log
> for the database to free up some space.'
> running backup log dbname with truncate_only sorts it out. All's I want
> from my back up is to take a copy of some small databases every night.
> What I am doing wrong to cause the log files to continue growing. I'm a
> designer not an administrator so it is a little out of my area!
>

Log Error

Hi I am getting the follow error
'log file for database dbname is full, please backup the transaction log for
the database to free up some space.'
running backup log dbname with truncate_only sorts it out. All's I want from
my back up is to take a copy of some small databases every night. What I am
doing wrong to cause the log files to continue growing. I'm a designer not
an administrator so it is a little out of my area!
Hi Chris
You may have a fixed size allocated to the database and in some
circumstances this may not be sufficient.
The may help you understand the log file architecture:
http://msdn.microsoft.com/library/de...asp?frame=true
and the whole chapter is a good read!
http://msdn.microsoft.com/library/de...asp?frame=true
This may also helps:
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:O9744SeXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi I am getting the follow error
> 'log file for database dbname is full, please backup the transaction log
> for the database to free up some space.'
> running backup log dbname with truncate_only sorts it out. All's I want
> from my back up is to take a copy of some small databases every night.
> What I am doing wrong to cause the log files to continue growing. I'm a
> designer not an administrator so it is a little out of my area!
>
|||To add to John's response, you can set your database recovery model to
SIMPLE so that committed data are automatically removed from the log. The
SIMPLE recovery model is appropriate when your recovery plan is to restore
from your last full backup and it is acceptable to lose data modifications
made since the backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:O9744SeXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi I am getting the follow error
> 'log file for database dbname is full, please backup the transaction log
> for the database to free up some space.'
> running backup log dbname with truncate_only sorts it out. All's I want
> from my back up is to take a copy of some small databases every night.
> What I am doing wrong to cause the log files to continue growing. I'm a
> designer not an administrator so it is a little out of my area!
>