Locks and Blocks: 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.This is a multi-part message in MIME format.
--000300010805090603020106
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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/default.asp?url=/library/en-us/createdb/cm_8_des_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.
>
--000300010805090603020106
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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: 
</tt>
<blockquote><tt>ALTER DATABASE TSBASE set RECOVERY SIMPLE 
</tt></blockquote>
<tt>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: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp</a> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
wrytat wrote:
<blockquote cite="mid2629587A-6C8D-4A63-AB5E-ECAC182CDEAC@.microsoft.com"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</body>
</html>
--000300010805090603020106--|||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/default.asp?url=/library/en-us/createdb/cm_8_des_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.
> >
> >
>|||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
> --
>|||This is a multi-part message in MIME format.
--000303070709010108040507
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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
>>--
>>
>>
--000303070709010108040507
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Hemant Goswami wrote:
<blockquote cite="mid70310EFD-8DFC-4212-A588-EF601607BD2E@.microsoft.com"
type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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
--
</pre>
</blockquote>
</blockquote>
</body>
</html>
--000303070709010108040507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C571B2.66267180
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
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=20
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 --
--=_NextPart_000_0101_01C571B2.66267180
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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" 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 jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.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 --

--=_NextPart_000_0101_01C571B2.66267180--

Monday, March 19, 2012

Log File Full -> Back up Transaction Log

No comments:

Post a Comment

Locks and Blocks

Blog Archive

About Me