THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Would you like to be able to do minimally logged deletes?

I started a thread in two different private Microsoft newsgroups about minimal logging. There's been a lot of discussion, so now I'm bringing up the issue in public. In an effort to keep things focused, here are guidelines for the discussion:

  • This is not being advocated for production machines.
  • This is being advocated for testing.
  • What is being sought is to be able to quickly delete large amounts of data without bloating the transaction log.
  • Think of a delete being more like a truncate when a special keyword or flag is used.

Not everybody thinks this is necessary or a good idea. Others see value in having such a feature.

So, do you want an option to reduce the amount of logging for bulk deletes?

Published Sunday, July 12, 2009 10:35 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

daveballantyne said:

I think its not such a good idea as although you say "This is not being advocated for production machines",  it will happen and people will get stung.

July 13, 2009 4:47 AM
 

rlutt said:

I think this would be an awesome thing expecially if you could use the delete with a where cluase to only delete stuff say older than 3 months for testing purposes, yet not take an enternity to return since it all being logged.

July 13, 2009 10:04 AM
 

merrillaldrich said:

I'd love that, especially if it were implemented along the lines of the existing Bulk operations (bulk logged mode + bulk delete = minimal logging)

July 13, 2009 11:27 AM
 

Bernd Eckenfels said:

What is the drawback of incomplete Logging?

Unable to rollback on aborted deletes? Or is there also a more basic consitency problem involved?

Bernd

July 13, 2009 6:47 PM
 

John Paul Cook said:

It all depends on how this feature is implemented - if it ever is - to be able to say what the specific drawbacks are. It's probable that aborted deletes wouldn't roll back.

July 13, 2009 10:37 PM
 

ALZDBA said:

[quote]

This is being advocated for testing.

[/quote]

Such a guideline is absolute rubbish !

Remember what happened to updatable catalogs.... most of the problems occured in prod !

More and more, db management is being done by "the general public".

With the current dumb and dumber copy/paste - execute attitude, if something is possible, they will do it, no matter what the cost or impact will be.

I can see the need for the minimally logged deletes, but how do you manage that with point in time restores ?

Most of the PIT restores I need to perform, actually are restores of the dev db itself, where the testing teams did discover a "strange" behavior in the apps and needed to repro the situation to help fixing the software.

As long as the data is consistent, DRI is maintained, ... I guess it should be OK, but that would actually need a RID to be mentioned somewhere for the time of the operation itself.

It should be a fully supported feature and maybe be implemented as a "sp_configure" feature. Off by default.

July 14, 2009 2:42 AM
 

Mike C said:

As you mentioned we already have minimally logged table truncation (TRUNCATE TABLE statement).  Taking some limitations into consideration I don't see why we shouldn't be able to get similar minimally logged functionality out of DELETE statements in some circumstances, and personally I don't see how it would cause problems if we did have that functionality.

Of course there may be those who don't believe it's impossible and of course those who feel they need to be saved from themselves...

July 18, 2009 2:46 PM
 

RowlandG said:

Maybe a good place to start is eliminating the confusion about TRUNCATE being a "non-logged operation". That little phrase popped into quite a few resources over the years. At some point I believed it -- up until very recently when someone challenged that assertion.

Mea culpa.

July 21, 2009 9:27 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement