Log backup chain

Hi all, BOL states
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain. Until
a full database backup is created, the database is not protected from media
failure. Use manual log truncation only in very special circumstances, and
create a full database backup as soon as practical.
Case:
A daily full backup of a db occurs at 7 p.m. and tran log backup every
15min, and a job shrinks the log file (DBCC SHRINKFILE) daily at 6 a.m. What
should happen if I try to restore the database to a point of failure after 6
a.m. (say 11.45 a.m.)? Can I still continue restoring my tran log backups up
11:45 or everthing from 6 a.m onwards is lost?
I thought that truncating the log file manually would break the log backup
chain because inactive part of the log will be released. However, when I
tried to do so all seemed to be fine (no error messages). Surely, I
misunderstood something. Can anyone explain what "it breaks the log backup
chain" actually means?Hi,
If u studied the BOL then u should come to know that as u will do the
back up of the database transaction log automatically truncates and
flushes out the unused space.
When we execute any DML command sql writes it in transaction log and
issues a checkpoint and when we do backup the sql server clears all old
transaction records except the active portion of the transaction log.
So it will not harm u .u can do the point in time recovery.
U are doing transaction log backup every 15 min so suppose u had backup
at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
PM and finishes in few seconds.
So u have the backup till 7 PM and when u will run the truncate command
it will clear the logfile before 7 pm and u have the tlog backup after
7 pm.
So, now u have tlog back every 15 mins.In case u have a crash then u
have to apply the tlog after the restore of last backup back of the
database and then restore all the tlog after 7 PM.
Aslo study virtual logfile and truncate logfile from BOL
If u use the command with Tuncate_only it will shrink the logfile on
disk
No it will not break the chain because as u will do the backup of
database database issue a checkpoint and stores a entry in it self so
when u apply the tlog it will take the LSN after the backup.
Hope it helps u.
from
Killer.|||Thanks doller for your time. But what if dbcc shrinkfile was executed between
tlob backups. e.g
Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
I understand what after tran log backup, inactive part of the log gets
removed and we don't need to run the shrinkfile command. But I am looking at
the server that has this configs (?) . I have tried to restore the data,
wanting to see if it will cause any problems and it did not.
"doller" wrote:
> Hi,
> If u studied the BOL then u should come to know that as u will do the
> back up of the database transaction log automatically truncates and
> flushes out the unused space.
> When we execute any DML command sql writes it in transaction log and
> issues a checkpoint and when we do backup the sql server clears all old
> transaction records except the active portion of the transaction log.
> So it will not harm u .u can do the point in time recovery.
> U are doing transaction log backup every 15 min so suppose u had backup
> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> PM and finishes in few seconds.
> So u have the backup till 7 PM and when u will run the truncate command
> it will clear the logfile before 7 pm and u have the tlog backup after
> 7 pm.
> So, now u have tlog back every 15 mins.In case u have a crash then u
> have to apply the tlog after the restore of last backup back of the
> database and then restore all the tlog after 7 PM.
> Aslo study virtual logfile and truncate logfile from BOL
> If u use the command with Tuncate_only it will shrink the logfile on
> disk
> No it will not break the chain because as u will do the backup of
> database database issue a checkpoint and stores a entry in it self so
> when u apply the tlog it will take the LSN after the backup.
>
> Hope it helps u.
> from
> Killer.
>|||Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> Thanks doller for your time. But what if dbcc shrinkfile was executed between
> tlob backups. e.g
> Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> I understand what after tran log backup, inactive part of the log gets
> removed and we don't need to run the shrinkfile command. But I am looking at
> the server that has this configs (?) . I have tried to restore the data,
> wanting to see if it will cause any problems and it did not.
>
> "doller" wrote:
>> Hi,
>> If u studied the BOL then u should come to know that as u will do the
>> back up of the database transaction log automatically truncates and
>> flushes out the unused space.
>> When we execute any DML command sql writes it in transaction log and
>> issues a checkpoint and when we do backup the sql server clears all old
>> transaction records except the active portion of the transaction log.
>> So it will not harm u .u can do the point in time recovery.
>> U are doing transaction log backup every 15 min so suppose u had backup
>> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> PM and finishes in few seconds.
>> So u have the backup till 7 PM and when u will run the truncate command
>> it will clear the logfile before 7 pm and u have the tlog backup after
>> 7 pm.
>> So, now u have tlog back every 15 mins.In case u have a crash then u
>> have to apply the tlog after the restore of last backup back of the
>> database and then restore all the tlog after 7 PM.
>> Aslo study virtual logfile and truncate logfile from BOL
>> If u use the command with Tuncate_only it will shrink the logfile on
>> disk
>> No it will not break the chain because as u will do the backup of
>> database database issue a checkpoint and stores a entry in it self so
>> when u apply the tlog it will take the LSN after the backup.
>>
>> Hope it helps u.
>> from
>> Killer.
>>|||Thanx Tibor, I have visited your site many times before but did see this
article. I follow now. That's why somitimes when you do
"dbcc shrinkfile" and the size of the tlog is not reduced we need to run
"backup log xxx with truncate_only" and than dbcc shrinkfile.
Thanx again.
"Tibor Karaszi" wrote:
> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
> > tlob backups. e.g
> >
> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> >
> > I understand what after tran log backup, inactive part of the log gets
> > removed and we don't need to run the shrinkfile command. But I am looking at
> > the server that has this configs (?) . I have tried to restore the data,
> > wanting to see if it will cause any problems and it did not.
> >
> >
> > "doller" wrote:
> >
> >> Hi,
> >> If u studied the BOL then u should come to know that as u will do the
> >> back up of the database transaction log automatically truncates and
> >> flushes out the unused space.
> >> When we execute any DML command sql writes it in transaction log and
> >> issues a checkpoint and when we do backup the sql server clears all old
> >> transaction records except the active portion of the transaction log.
> >> So it will not harm u .u can do the point in time recovery.
> >> U are doing transaction log backup every 15 min so suppose u had backup
> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> >> PM and finishes in few seconds.
> >>
> >> So u have the backup till 7 PM and when u will run the truncate command
> >> it will clear the logfile before 7 pm and u have the tlog backup after
> >> 7 pm.
> >> So, now u have tlog back every 15 mins.In case u have a crash then u
> >> have to apply the tlog after the restore of last backup back of the
> >> database and then restore all the tlog after 7 PM.
> >>
> >> Aslo study virtual logfile and truncate logfile from BOL
> >>
> >> If u use the command with Tuncate_only it will shrink the logfile on
> >> disk
> >> No it will not break the chain because as u will do the backup of
> >> database database issue a checkpoint and stores a entry in it self so
> >> when u apply the tlog it will take the LSN after the backup.
> >>
> >>
> >>
> >> Hope it helps u.
> >> from
> >> Killer.
> >>
> >>
>|||> That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
Exactly. It's all in understanding how the virtual log files work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:362F54BB-0F65-404D-BE62-F35BCCB9249A@.microsoft.com...
> Thanx Tibor, I have visited your site many times before but did see this
> article. I follow now. That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
> Thanx again.
> "Tibor Karaszi" wrote:
>> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any
>> of
>> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit
>> on
>> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
>> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
>> > tlob backups. e.g
>> >
>> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
>> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
>> >
>> > I understand what after tran log backup, inactive part of the log gets
>> > removed and we don't need to run the shrinkfile command. But I am looking at
>> > the server that has this configs (?) . I have tried to restore the data,
>> > wanting to see if it will cause any problems and it did not.
>> >
>> >
>> > "doller" wrote:
>> >
>> >> Hi,
>> >> If u studied the BOL then u should come to know that as u will do the
>> >> back up of the database transaction log automatically truncates and
>> >> flushes out the unused space.
>> >> When we execute any DML command sql writes it in transaction log and
>> >> issues a checkpoint and when we do backup the sql server clears all old
>> >> transaction records except the active portion of the transaction log.
>> >> So it will not harm u .u can do the point in time recovery.
>> >> U are doing transaction log backup every 15 min so suppose u had backup
>> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> >> PM and finishes in few seconds.
>> >>
>> >> So u have the backup till 7 PM and when u will run the truncate command
>> >> it will clear the logfile before 7 pm and u have the tlog backup after
>> >> 7 pm.
>> >> So, now u have tlog back every 15 mins.In case u have a crash then u
>> >> have to apply the tlog after the restore of last backup back of the
>> >> database and then restore all the tlog after 7 PM.
>> >>
>> >> Aslo study virtual logfile and truncate logfile from BOL
>> >>
>> >> If u use the command with Tuncate_only it will shrink the logfile on
>> >> disk
>> >> No it will not break the chain because as u will do the backup of
>> >> database database issue a checkpoint and stores a entry in it self so
>> >> when u apply the tlog it will take the LSN after the backup.
>> >>
>> >>
>> >>
>> >> Hope it helps u.
>> >> from
>> >> Killer.
>> >>
>> >>
>>