|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Log backup does not truncate the log fileHi,
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:
Show quoteHide quote > Hi, To clear unused virtual files you can try:> 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 > > 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! Show quoteHide quote "Zero One" <efes_ec***@hotmail.com> wrote in message news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... > 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/ Leila wrote:
Show quoteHide quote > I know this command, but based on explanation in BOL, the BACKUP LOG must The backup only marks unused VLFs as deleted but does not remove them. > truncate the log file automatically. Is it true or I have misunderstood > this? > Thanks! > > > "Zero One" <efes_ec***@hotmail.com> wrote in message > news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >> 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/ > > The shrink does. You mean the shrink does not break the log chain?
Show quoteHide quote "Zero One" <efes_ec***@hotmail.com> wrote in message news:OyIUXbUJHHA.4376@TK2MSFTNGP03.phx.gbl... > 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_ec***@hotmail.com> wrote in message >> news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >>> 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/ >> >> > > The backup only marks unused VLFs as deleted but does not remove them. The > shrink does. Leila wrote:
Show quoteHide quote > You mean the shrink does not break the log chain? Quoting from the aforementioned KB:> > > "Zero One" <efes_ec***@hotmail.com> wrote in message > news:OyIUXbUJHHA.4376@TK2MSFTNGP03.phx.gbl... >> 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_ec***@hotmail.com> wrote in message >>> news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >>>> 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/ >>> >> The backup only marks unused VLFs as deleted but does not remove them. The >> shrink does. > > "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. Show quoteHide quote "Leila" <Lei***@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_ec***@hotmail.com> wrote in message > news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >> 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/ > > Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen! Show quoteHide quote "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" <Lei***@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_ec***@hotmail.com> wrote in message >> news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >>> 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/ >> >> > > What do you mean?
Show quoteHide quote "Leila" <Lei***@hotpop.com> wrote in message news:OuDzdfZJHHA.1240@TK2MSFTNGP03.phx.gbl... > Thanks every body for clarifications :-) > BTW, I cannot browse your blog Kalen! "Leila" <Lei***@hotpop.com> wrote in message You misunderstood.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? 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. Show quoteHide quote > Thanks! > > > "Zero One" <efes_ec***@hotmail.com> wrote in message > news:eF735SUJHHA.4112@TK2MSFTNGP04.phx.gbl... >> 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/ > >  
Thread options
Other interesting topics
Need to create an "offline" database, using production SQL2000 dat
About difference between log shipping standby and active/passive c Sorting the "insides" of a UNION Correlated Subquery Browse mssqlsystemresource.mdf (SQL Server's Resource Database) sql server backup.. not able to finish Data Shaping Question (Syntax) Getting results from 2 tables, determined by a 3rd SQL 2005 Defrag Script search phrase as google does |
|||||||||||||||||||||||
