SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minimal Logging


Minimal Logging

Author
Message
harsha.majety
harsha.majety
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 448
Comments posted to this topic are about the item Minimal Logging
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3813 Visits: 1717
TRUNCATE TABLE is not a minimally-logged operation. It's logged exactly the same way in all recovery models. It's fully logged, but very efficiently logged, including using the deferred-drop mechanism.

Minimal logging is a distinctly separate set of code paths in the Engine, that does things like setting the ML_MAP bits for the extents involved. That does not happen for TRUNCATE TABLE.

It's a common misconception that TRUNCATE is minimally-logged or non-logged.

And everything I say above also applies to DROP TABLE and DROP INDEX too.

I've explained this in several places online, including at http://sqlmag.com/blog/why-do-truncate-and-drop-table-complete-so-fast

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
harsha.majety
harsha.majety
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 448
Thank you for clarifying Paul. Read your article on sqlmag.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 487
I beleive Microsoft refers to TRUNCATES as a metadata only operation. They LIST truncates in the "OTHER MINIMALLY LOGGED and METADATA ONLY operations" section of the document.

http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Good article Smile

Steven Neumersky, CBIP, MCITP
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3813 Visits: 1717
I know - it's very misleading because it doesn't actually do minimal logging for it.

If you watch the log records, it's a metadata-only operation to unhook the allocation unit and put it on the deferred-drop queue. Go back into the log a few seconds later and you'll see all the pages being deallocated. I demo'd it in my Pluralsight course on logging.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
SQLCharger
SQLCharger
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 1422
I'd also add the clarification that 'merge' is supported only when it comes to new extent allocations.

Cheers,

JohnA

MCM: SQL2008
kevaburg
kevaburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1719 Visits: 1015
If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91350 Visits: 41151
kevaburg (9/29/2013)
If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?


I know this is a old post but the answer is "NO". It doesn't break the log chain at all. The ONLY thing that you need to be aware of is that if a minimally logged transaction (it only takes 1) is included in a particular log file backup, then you can't restore to a point in time within that log file backup. You must either stop the restore before that log file or use the whole log file.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91350 Visits: 41151
I also state that the INSERT...SELECT minimal logging isn't worth a hoot. According to a source at MS, it does "double writing" in the MDF file (that would be just as bad or worse than writing to a log file) and it still blows the log file space out if it's not already allocated. I just completed some testing for another purpose where the MDFF and LDF files were both started at 100MB with a growth of 100MB and the heap table met all of the conditions. The DB is in the SIMPLE Recovery Model. Doing a given SELECT INTO of a nearly 300MB table cause the MDF to grow to 300MB (as expected) and the log file remained at 100MB (as expected).After resetting the database to it's starting point, the same datasource was used for INSERT... SELECT. First, that took 45 seconds instead of the 7.5 seconds the SELECT INTO did. Many MS articles have stated that this would happen because it was never designed performance improvement (Seriously???). It also cause the LDF to grow from 100MB to 1600MB, which MS also documented as a "safety", which is really a left over of reallocation (paraphrasing their words for simplicity).And yes... this occurred with the proper use of WITH (TABLOCK) and, even though it's not supposed to be needed for a heap, I tried it with TF-610. Same results.I've not tested INSERT...SELECT for minimal logging so I don't know if it causes the same non-minimal problems (or results if you rather not call documented functionality a problem) but, if it hasn't changed, it's a worthless feature to me. No performance improvement and unnecessary bloating of the log file don't sound like anything really useful. Yeah... it might help backups a bit in the Bulk Logged mode but that's overwhelmed by the problems this feature exudes.Oh... and upon further reading, some say that you can't do big stuff with it or will result in the problems I just spoke about. I'll try that latter, as well but REALLY? It can't handle a piddley 300MB transfer?Doesn't seem at all useful and, if it has similar problems in 2012 (just got there recently), I know I won't be using it there, either. SELECT INTO and some of the other methods are much more effective for the amount of data that I do end up loading. I'll gladly take the final log file hit of the transfer of data from staging tables to permanent tables compared to the hits INSERT...SELECT has even when I'm in a SIMPLE Recovery Model for staging databases.

UPDATE: SEE POST BELOW.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91350 Visits: 41151
Heh... man, I'm glad I could find the post above so that I could make this update.

I couldn't, for the life of me, get minimal logging to work using INSERT/SELECT on an existing but empty table with a clustered index on it. The problem was, I was using a Tally function to generate the rows and, to control the number of rows, I was passing a variable to the Tally function, Finally remembering that a variable on something in the FROM or WHERE clause might cause a bad execution plan because the value of the variable isn't known at compile time, I added OPTION (RECOMPILE) to the table population query and, VOILA!, it finally worked as expect. Built a 7 million row table in under 3 minutes with the clustered index in-place on my laptop.

That little nuance isn't in BOL for minimal logging anywhere. To be on the safe side, I recommend using OPTION(RECOMPILE) all the time so that you don't have to remember to add it in in certain cases. The recompile time is trivial compared to the huge savings that minimal logging can give you.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search