<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Disaster Recovery
»
Does the full backup clear/truncate the...
Does the full backup clear/truncate the t-log?
Rate Topic
Display Mode
Topic Options
Author
Message
JakeSA
JakeSA
Posted Monday, November 14, 2011 8:50 AM
Grasshopper
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, November 14, 2011 8:54 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357,
Visits: 9,535
You're right.
http://www.sqlservercentral.com/articles/64582/
Post #1205243
GilaMonster
GilaMonster
Posted Monday, November 14, 2011 9:22 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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
JakeSA
JakeSA
Posted Monday, November 14, 2011 9:32 AM
Grasshopper
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, November 14, 2011 9:34 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357,
Visits: 9,535
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
GilaMonster
GilaMonster
Posted Monday, November 14, 2011 9:37 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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
JakeSA
JakeSA
Posted Monday, November 14, 2011 9:38 AM
Grasshopper
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
GilaMonster
GilaMonster
Posted Monday, November 14, 2011 9:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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
viiki.seth
viiki.seth
Posted Monday, November 14, 2011 9:43 AM
Valued 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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
