Backup Log and Shrinking of the ldf file.

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
MFirstly, 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 mor
e
>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 woul
d
>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
>
>
>|||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|||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 n
eed to do multiple backups. Thank you very much for your explanation and sug
gestions.
"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 enoug
h so that it doesn't exceed that size and then leave it alone. (See Tibor's "don't shr
ink" page: http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
Now that that's out of the way... The transaction log (logical) is a circul
ar structure. When it gets to the end of the physical file it wraps back ar
ound to the start of the physical file (assuming there are no transactions a
t the start of the physical file, ie. that they've been truncated from a pre
vious BACKUP statement, otherwise it will try to do an auto-grow if you have
n't restricted it). When you backup the transaction log, it is automaticall
y 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 t
he physical file). When you shrink the log file (with DBCC SHRINKFILE) it s
hrinks the physical file from the end back to that last VLF that contains tr
ansactions. 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 co
mmitted, 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 co
rrespondingly 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 pr
oduction transaction logs - bad, bad, naughty, naughty. Just set it and lea
ve 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 transacti
on 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