SQLServerCentral.com / Disaster Recovery / Database Design / Does the full backup clear/truncate the t-log? / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 13:49:01 GMT20RE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspxsp_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.Mon, 14 Nov 2011 09:43:44 GMTviiki.sethRE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote][b]JakeSA (11/14/2011)[/b][hr]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. [/quote]Database options meaning???????Mon, 14 Nov 2011 09:40:20 GMTGilaMonsterRE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote]The log doesn't autoshrink to free the space back to the OS.The vlf are marked as resusable.[/quote]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!Mon, 14 Nov 2011 09:38:48 GMTJakeSARE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote][b]JakeSA (11/14/2011)[/b][hr][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?[/quote]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.Mon, 14 Nov 2011 09:37:03 GMTGilaMonsterRE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote][b]JakeSA (11/14/2011)[/b][hr][quote]p.s. Nothing deletes inactive log records from the log. Ever.[/quote]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?[/quote]The log doesn't autoshrink to free the space back to the OS.The vlf are marked as resusable.Mon, 14 Nov 2011 09:34:54 GMTNinja's_RGR'usRE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote]p.s. Nothing deletes inactive log records from the log. Ever.[/quote]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?Mon, 14 Nov 2011 09:32:09 GMTJakeSARE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspx[quote][b]JakeSA (11/14/2011)[/b][hr]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?[/quote][url]http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx[/url][url]http://sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx[/url]p.s. Nothing deletes inactive log records from the log. Ever.Mon, 14 Nov 2011 09:22:21 GMTGilaMonsterRE: Does the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspxYou're right.[url]http://www.sqlservercentral.com/articles/64582/[/url]Mon, 14 Nov 2011 08:54:39 GMTNinja's_RGR'usDoes the full backup clear/truncate the t-log?http://www.sqlservercentral.com/Forums/Topic1205237-384-1.aspxMy 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?Mon, 14 Nov 2011 08:50:54 GMTJakeSA