Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / January 2008

Tip: Looking for answers? Try searching our database.

Backing up log files

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ant - 29 Jan 2008 08:01 GMT
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 Karaszi - 29 Jan 2008 08:05 GMT
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".

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
>
[quoted text clipped - 6 lines]
> Many thanks for helping me understand what is happening here
> Ant
Uri Dimant - 29 Jan 2008 08:24 GMT
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

> 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
[quoted text clipped - 12 lines]
>> Many thanks for helping me understand what is happening here
>> Ant
Tibor Karaszi - 29 Jan 2008 08:47 GMT
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. :-).
Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
[quoted text clipped - 16 lines]
>>> Many thanks for helping me understand what is happening here
>>> Ant
Uri Dimant - 29 Jan 2008 09:02 GMT
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

> Hi Uri,
>
[quoted text clipped - 54 lines]
>>>> Many thanks for helping me understand what is happening here
>>>> Ant
Kalen Delaney - 29 Jan 2008 21:19 GMT
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.

Signature

HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

> Hi Uri,
>
[quoted text clipped - 54 lines]
>>>> Many thanks for helping me understand what is happening here
>>>> Ant
Tibor Karaszi - 30 Jan 2008 14:59 GMT
Hi Kalen,

Thanks. :-)

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi Tibor
>
[quoted text clipped - 49 lines]
>>>>> Many thanks for helping me understand what is happening here
>>>>> Ant
Kalen Delaney - 30 Jan 2008 17:23 GMT
You're welcome.
:-)

Signature

HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

> Hi Kalen,
>
[quoted text clipped - 65 lines]
>>>>>> Many thanks for helping me understand what is happening here
>>>>>> Ant
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.