Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
Leila
Leila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected this
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB>)
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/
|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB>)
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/
|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.
|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.
|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).
|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.

> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>