November 14, 2011 at 8:50 am
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?
November 14, 2011 at 8:54 am
You're right.
November 14, 2011 at 9:22 am
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/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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 9:32 am
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?
November 14, 2011 at 9:34 am
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.
November 14, 2011 at 9:37 am
JakeSA (11/14/2011)
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 9:38 am
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!
November 14, 2011 at 9:40 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 9:43 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply