Backing up log files

Hi,
I'd like to know what happens to a log file when you back it up. I received
an error that my log file had grown too much & that I should back it up,
which I did & I no longer received the error.
But what happens when you simply back up a log file? Does it also truncate
unused space or what? It obviously doesn't simply make a backup of it.
Many thanks for helping me understand what is happening here
AntBACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
reason don't want to do transaction log backup then set the recovery model for the database to
"simple".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
> Hi,
> I'd like to know what happens to a log file when you back it up. I received
> an error that my log file had grown too much & that I should back it up,
> which I did & I no longer received the error.
> But what happens when you simply back up a log file? Does it also truncate
> unused space or what? It obviously doesn't simply make a backup of it.
> Many thanks for helping me understand what is happening here
> Ant|||Tibor
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file).
I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
virtual logs file that LOG file is built by and delete only inactive
(written to disk) transaction
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file). If you for some reason don't want to do transaction log backup then
> set the recovery model for the database to "simple".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>|||Hi Uri,
It all depends on what abstraction level we want to be. :-)
At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate", whichever
in English gets the message through). I prefer to say "empty", since I believe it makes it clearer
that the file isn't made smaller.
There are of course much more details what happens when you BACKUP LOG. I stay away from those
details when I see a basic question and I suspect the OP has limited experience with transaction log
internals - like virtual log files. The reasoning is that more details will only cloud the message.
:-)
As for the details, this is how I believe it works. Anyone is of course free to add and correct,
below if off the top of my head:
A VLF can be in four states:
A. Never ever used
B. Used, and all log records are prior than the last checkpoint and have also been written to disk.
C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
D. The active log (where the current log record, "head of the log", is).
BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever the
head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C and D
have status 2.
Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
tend to stick with the higher abstraction level for basic questions. :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
> LOG file is built by and delete only inactive (written to disk) transaction
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>|||Tibor
The ability to think on abstaction level given from God, I mean you canot
learn this. :-)
And you I think have it.
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file
Yep, that it was my understanding as well
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Tibor
The two middle states have nothing to do with Checkpoint. The have to do
with whether those transactions have been backed up, so the log space can be
reused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Kalen,
Thanks. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> The two middle states have nothing to do with Checkpoint. The have to do with whether those
> transactions have been backed up, so the log space can be reused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate",
>> whichever in English gets the message through). I prefer to say "empty", since I believe it makes
>> it clearer that the file isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG. I stay away from those
>> details when I see a basic question and I suspect the OP has limited experience with transaction
>> log internals - like virtual log files. The reasoning is that more details will only cloud the
>> message. :-)
>> As for the details, this is how I believe it works. Anyone is of course free to add and correct,
>> below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have also been written to
>> disk.
>> C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
>> virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever
>> the head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C
>> and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
>> tend to stick with the higher abstraction level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
>> LOG file is built by and delete only inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>|||You're welcome.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWx%23pC1YIHA.3880@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
> Thanks. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> The two middle states have nothing to do with Checkpoint. The have to do
>> with whether those transactions have been backed up, so the log space can
>> be reused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the
>> log" (or "truncate", whichever in English gets the message through). I
>> prefer to say "empty", since I believe it makes it clearer that the file
>> isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG.
>> I stay away from those details when I see a basic question and I suspect
>> the OP has limited experience with transaction log internals - like
>> virtual log files. The reasoning is that more details will only cloud
>> the message. :-)
>> As for the details, this is how I believe it works. Anyone is of course
>> free to add and correct, below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have
>> also been written to disk.
>> C. Used, but some log records are more recent than last checkpoint
>> and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just
>> allow for re-use of the virtual log file. Basically turning C above into
>> B. A and B can be re-used (overwritten whenever the head of the log
>> moves to this virtual log file). These have status 0 in DBCC LOGINFO,
>> while C and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or
>> whiteboard). Which is why I tend to stick with the higher abstraction
>> level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to
>> re-use virtual logs file that LOG file is built by and delete only
>> inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file). If you for some reason don't want to do transaction log
>> backup then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of
>> it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>>
>