Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
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: Yesterday @ 9:02 AM
Points: 2,040, Visits: 1,668
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: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
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: Yesterday @ 9:02 AM
Points: 2,040, Visits: 1,668
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:40 AM
Points: 324, Visits: 534
If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?
Post #1499742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse