On of our Production servers, we have noticed that the actual shrinking of
the physical log file (.ldf) does not happen if we execute the backup log
statement only once. It seems we have to run the transaction log backup more
than once or multiple times to shrink the log file. The first time we
execute the backup log statement it seems that the DBCC Loginfo shows the
status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
subsequent execution of the backup log statement again a second time, the
file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases
many times to shrink the file? Is this by design? I am trying to understand
how the log and VLFs occur when it comes to backup and shrink and the need
to run the backup log statement twice to shrink the file physically. I would
like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side configuration
that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly
appreciated.
Thanks
MThis is a multi-part message in MIME format.
--010809070604000608040104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Firstly, why are you shrinking a production transaction log? This is a
bad thing to do. Just set it to the max size to which you want it to
grow, back it up regularly enough so that it doesn't exceed that size
and then leave it alone. (See Tibor's "don't shrink" page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
Now that that's out of the way... The transaction log (logical) is a
circular structure. When it gets to the end of the physical file it
wraps back around to the start of the physical file (assuming there are
no transactions at the start of the physical file, ie. that they've been
truncated from a previous BACKUP statement, otherwise it will try to do
an auto-grow if you haven't restricted it). When you backup the
transaction log, it is automatically truncated but some logical part of
it (VLF) is still the "active" logical file. This is where the new
transactions start from (not the beginning of the physical file). When
you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical
file from the end back to that last VLF that contains transactions. So,
if the active VLF is somewhere near the end of the physical file, it's
not going to shrink very much, but when more transactions are committed,
the log will "wrap" back around to the start of the physical file. If
you did a BACKUP LOG and DBCC SHRINKFILE at this point then the physical
file would be able to shrink down considerably.
This is most likely the reason you're needing to do multiple BACKUPs
(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a
point - because you need the transactions to wrap back around to the
start of the physical file.
However, it should all be a moot point because you shouldn't be
shrinking production transaction logs - bad, bad, naughty, naughty.
Just set it and leave it alone. And if you want it to stay tiny just
back it up more regularly. Or if you want it to stay tiny and don't
care about keeping the transaction log, then put the database in SIMPLE
recovery mode (just make sure you do full DB backups often enough to
minimise data loss).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Meher wrote:
>Hi:
>On of our Production servers, we have noticed that the actual shrinking of
>the physical log file (.ldf) does not happen if we execute the backup log
>statement only once. It seems we have to run the transaction log backup more
>than once or multiple times to shrink the log file. The first time we
>execute the backup log statement it seems that the DBCC Loginfo shows the
>status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
>subsequent execution of the backup log statement again a second time, the
>file is shrunk (dbcc loginfo shows the status as 0).
>Any reason why we need to run the Tran log backup twice or in some cases
>many times to shrink the file? Is this by design? I am trying to understand
>how the log and VLFs occur when it comes to backup and shrink and the need
>to run the backup log statement twice to shrink the file physically. I would
>like to avoid that if I can and if it is possible.
>Additionally i am wondering if there is any db or server side configuration
>that causes this to happen.
>The Servers are all running SQL Server 2000 (both SP3 and SP4).
>MVPS, Please provide your valuable knowledge. Any insight is highly
>appreciated.
>Thanks
>M
>
>
>
--010809070604000608040104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Firstly, why are you shrinking a production transaction log? This
is a bad thing to do. Just set it to the max size to which you want it
to grow, back it up regularly enough so that it doesn't exceed that
size and then leave it alone. (See Tibor's "don't shrink" page:
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.karaszi.com/SQLServer/info_dont_shrink.asp</a>)<br>">http://www.karaszi.com/SQLServer/info_dont_shrink.asp">http://www.karaszi.com/SQLServer/info_dont_shrink.asp</a>)<br>
<br>
Now that that's out of the way... The transaction log (logical) is a
circular structure. When it gets to the end of the physical file it
wraps back around to the start of the physical file (assuming there are
no transactions at the start of the physical file, ie. that they've
been truncated from a previous BACKUP statement, otherwise it will try
to do an auto-grow if you haven't restricted it). When you backup the
transaction log, it is automatically truncated but some logical part of
it (VLF) is still the "active" logical file. This is where the new
transactions start from (not the beginning of the physical file). When
you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical
file from the end back to that last VLF that contains transactions.
So, if the active VLF is somewhere near the end of the physical file,
it's not going to shrink very much, but when more transactions are
committed, the log will "wrap" back around to the start of the physical
file. If you did a BACKUP LOG and DBCC SHRINKFILE at this point then
the physical file would be able to shrink down considerably.<br>
<br>
This is most likely the reason you're needing to do multiple BACKUPs
(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a
point - because you need the transactions to wrap back around to the
start of the physical file.<br>
<br>
However, it should all be a moot point because you shouldn't be
shrinking production transaction logs - bad, bad, naughty, naughty.
Just set it and leave it alone. And if you want it to stay tiny just
back it up more regularly. Or if you want it to stay tiny and don't
care about keeping the transaction log, then put the database in SIMPLE
recovery mode (just make sure you do full DB backups often enough to
minimise data loss).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Meher wrote:
<blockquote cite="midelPEWe1YGHA.1352@.TK2MSFTNGP05.phx.gbl" type="cite">
<pre wrap="">Hi:
On of our Production servers, we have noticed that the actual shrinking of
the physical log file (.ldf) does not happen if we execute the backup log
statement only once. It seems we have to run the transaction log backup more
than once or multiple times to shrink the log file. The first time we
execute the backup log statement it seems that the DBCC Loginfo shows the
status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
subsequent execution of the backup log statement again a second time, the
file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases
many times to shrink the file? Is this by design? I am trying to understand
how the log and VLFs occur when it comes to backup and shrink and the need
to run the backup log statement twice to shrink the file physically. I would
like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side configuration
that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly
appreciated.
Thanks
M
</pre>
</blockquote>
</body>
</html>
--010809070604000608040104--|||Hi,
What is the size of production database.
Make a matinance plan and configure to shrink the logfile after full
backup.Then schedule transaction log backup.
this will help u.
from
Doller|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C6632E.B6BDDF20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Well its not me who is shrinking the files but my customer. However I =should say the Mike this is a beautiful explanation that I have read =about why i need to do multiple backups. Thank you very much for your =explanation and suggestions. "Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:elRBaP2YGHA.2376@.TK2MSFTNGP03.phx.gbl...
Firstly, why are you shrinking a production transaction log? This is =a bad thing to do. Just set it to the max size to which you want it to =grow, back it up regularly enough so that it doesn't exceed that size =and then leave it alone. (See Tibor's "don't shrink" page: =http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
Now that that's out of the way... The transaction log (logical) is a =circular structure. When it gets to the end of the physical file it =wraps back around to the start of the physical file (assuming there are =no transactions at the start of the physical file, ie. that they've been =truncated from a previous BACKUP statement, otherwise it will try to do =an auto-grow if you haven't restricted it). When you backup the =transaction log, it is automatically truncated but some logical part of =it (VLF) is still the "active" logical file. This is where the new =transactions start from (not the beginning of the physical file). When =you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical =file from the end back to that last VLF that contains transactions. So, =if the active VLF is somewhere near the end of the physical file, it's =not going to shrink very much, but when more transactions are committed, =the log will "wrap" back around to the start of the physical file. If =you did a BACKUP LOG and DBCC SHRINKFILE at this point then the physical =file would be able to shrink down considerably.
This is most likely the reason you're needing to do multiple BACKUPs =(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a =point - because you need the transactions to wrap back around to the =start of the physical file.
However, it should all be a moot point because you shouldn't be =shrinking production transaction logs - bad, bad, naughty, naughty. =Just set it and leave it alone. And if you want it to stay tiny just =back it up more regularly. Or if you want it to stay tiny and don't =care about keeping the transaction log, then put the database in SIMPLE =recovery mode (just make sure you do full DB backups often enough to =minimise data loss).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Meher wrote: Hi:
On of our Production servers, we have noticed that the actual shrinking =of the physical log file (.ldf) does not happen if we execute the backup =log statement only once. It seems we have to run the transaction log backup =more than once or multiple times to shrink the log file. The first time we execute the backup log statement it seems that the DBCC Loginfo shows =the status of the VLF as 2 and therefore we cannot shrink the ldf file. On a =
subsequent execution of the backup log statement again a second time, =the file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases =
many times to shrink the file? Is this by design? I am trying to =understand how the log and VLFs occur when it comes to backup and shrink and the =need to run the backup log statement twice to shrink the file physically. I =would like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side =configuration that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly appreciated.
Thanks
M
--=_NextPart_000_000F_01C6632E.B6BDDF20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Well its not me who is shrinking the =files but my customer. However I should say the Mike this is a beautiful explanation =that I have read about why i need to do multiple backups. Thank you very much =for your explanation and suggestions.
"Mike Hodgson"
--mike =hodgsonhttp://sqlnerd.blogspot.com Meher wrote: Hi:
On of our Production servers, we have noticed that the actual shrinking =of the physical log file (.ldf) does not happen if we execute the backup =log statement only once. It seems we have to run the transaction log backup =more than once or multiple times to shrink the log file. The first time we execute the backup log statement it seems that the DBCC Loginfo shows =the status of the VLF as 2 and therefore we cannot shrink the ldf file. On a =subsequent execution of the backup log statement again a second time, =the file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases =many times to shrink the file? Is this by design? I am trying to =understand how the log and VLFs occur when it comes to backup and shrink and the =need to run the backup log statement twice to shrink the file physically. I =would like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side =configuration that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly appreciated.
Thanks
M
--=_NextPart_000_000F_01C6632E.B6BDDF20--
No comments:
Post a Comment