Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minimal Logging


Minimal Logging

Author
Message
harsha.majety
harsha.majety
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 448
Comments posted to this topic are about the item Minimal Logging
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

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

Group: General Forum Members
Points: 2162 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

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

Cheers,

JohnA

MCM: SQL2008
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 920
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45056 Visits: 39902
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45056 Visits: 39902
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.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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