Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Minimal Logging Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 4, 2016 7:54 PM
Points: 9, Visits: 448
Comments posted to this topic are about the item Minimal Logging
Post #1488290
Posted Monday, August 26, 2013 8:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 12, 2016 9:27 AM
Points: 2,053, Visits: 1,712
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
Post #1488410
Posted Monday, August 26, 2013 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 4, 2016 7:54 PM
Points: 9, Visits: 448
Thank you for clarifying Paul. Read your article on sqlmag.
Post #1488434
Posted Monday, August 26, 2013 10:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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 :)

Steven Neumersky, CBIP, MCITP
Post #1488440
Posted Monday, August 26, 2013 10:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 12, 2016 9:27 AM
Points: 2,053, Visits: 1,712
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
Post #1488445
Posted Tuesday, August 27, 2013 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
I'd also add the clarification that 'merge' is supported only when it comes to new extent allocations.

Cheers,

JohnA

MCM: SQL2008
Post #1488623
Posted Sunday, September 29, 2013 3:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 440, Visits: 814
If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?
Post #1499742
Posted Sunday, August 21, 2016 9:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 42,077, Visits: 39,461
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1811139
Posted Sunday, August 21, 2016 9:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 42,077, Visits: 39,461
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1811140
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse