Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does the full backup clear/truncate the t-log? Expand / Collapse
Author
Message
Posted Monday, November 14, 2011 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 20, 2011 11:04 AM
Points: 13, Visits: 37
My understanding is only the t-log backup will truncate the log and delete the inactive log entries in the t-log, but I still encounter DBAs who say the full database backup will do so for its t-log. Anyone know for sure?
Post #1205237
Posted Monday, November 14, 2011 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
You're right.

http://www.sqlservercentral.com/articles/64582/
Post #1205243
Posted Monday, November 14, 2011 9:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
JakeSA (11/14/2011)
My understanding is only the t-log backup will truncate the log and delete the inactive log entries in the t-log, but I still encounter DBAs who say the full database backup will do so for its t-log. Anyone know for sure?


http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx
http://sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx


p.s. Nothing deletes inactive log records from the log. Ever.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1205303
Posted Monday, November 14, 2011 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 20, 2011 11:04 AM
Points: 13, Visits: 37


p.s. Nothing deletes inactive log records from the log. Ever.


Hmmm, then how are the spaces freed up in the log file? Or maybe you mean that inactive logs entries are marked as having been backed up in a t-log backup so that they can be overwritten?
Post #1205319
Posted Monday, November 14, 2011 9:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
JakeSA (11/14/2011)


p.s. Nothing deletes inactive log records from the log. Ever.


Hmmm, then how are the spaces freed up in the log file? Or maybe you mean that inactive logs entries are marked as having been backed up in a t-log backup so that they can be overwritten?



The log doesn't autoshrink to free the space back to the OS.


The vlf are marked as resusable.
Post #1205325
Posted Monday, November 14, 2011 9:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
JakeSA (11/14/2011)
[quote]Or maybe you mean that inactive logs entries are marked as having been backed up in a t-log backup so that they can be overwritten?


I mean exactly what I say. Nothing ever removes log records from the log. A log backup marks active VLFs as inactive. Once inactive they can be overwritten.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1205331
Posted Monday, November 14, 2011 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 20, 2011 11:04 AM
Points: 13, Visits: 37



The log doesn't autoshrink to free the space back to the OS.


The vlf are marked as resusable.


Oh, got you. Which is what I realized. True, the log file does not have an option for auto shrink. That brings up another question. None of the database options (configurable in SSMS or sp_configure or sp_dboption) are applicable to the log file. They are applicable to the data (MDF and NDF) files only. Right?

This is a good discussion!
Post #1205333
Posted Monday, November 14, 2011 9:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
JakeSA (11/14/2011)
None of the database options (configurable in SSMS or sp_configure or sp_dboption) are applicable to the log file. They are applicable to the data (MDF and NDF) files only.


Database options meaning???????



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1205336
Posted Monday, November 14, 2011 9:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:11 PM
Points: 61, Visits: 158
sp_configure is used to read/change server wide settings.
sp_dboption is used to modify database level properties (not specific to a particular mdf or ndf).

so when you use sp_dboption to auto shrink a database (which is a bad option), all your database files will be shrunk.
Post #1205343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse