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


delete records in sql without entry in transaction log


delete records in sql without entry in transaction log

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62350 Visits: 19102
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies? You could be in a problematic situation.

I understand that it seems unnecessary, but disk space is relatively cheap, even for a few TBs. I'm not sure it's worth taking the chance of being in a situation you can't easily recovery from?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87093 Visits: 45267
wodom (10/5/2009)
We also have a situation where periodically we are need to bulk-delete an entire table (about 3 million records) and bulk-reload it from a file we receive from elsewhere.


Use TRUNCATE TABLE. That's precisely what it's there for - wiping an entire table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wodom
wodom
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 256
Steve Jones - Editor (10/5/2009)
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies?


If power dies, or other issue, no problem. Just start over with either the TRUNCATE or DROP, whichever is being used.



David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
I've heard a lot of people who should know better ask for this option. If MS ever implements it I think it should be:

SET CORRUPT_MY_DATABASE_AT_THE_WORST_POSSIBLE_TIME = True



And then again, I might be wrong ...
David Webb
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39703 Visits: 32639
David Webb-200187 (10/5/2009)
I've heard a lot of people who should know better ask for this option. If MS ever implements it I think it should be:

SET CORRUPT_MY_DATABASE_AT_THE_WORST_POSSIBLE_TIME = True


Ha!

Good one. And way too accurate. Although, I think you got the syntax wrong. Shouldn't it be set to = 1?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87093 Visits: 45267
wodom (10/5/2009)
Steve Jones - Editor (10/5/2009)
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies?


If power dies, or other issue, no problem. Just start over with either the TRUNCATE or DROP, whichever is being used.


It's far from that simple. In a truncate, what's logged is the page deallocations and the resulting changes to the allocation pages.

If you could run it without any logging and there's a crash half way through you can end up with pages that are marked as allocated but are not allocated. It's not just data changes that are logged, it's changes to the system tables, changes to the allocation pages, database structure changes, etc...

Edit: Clarified that I was talking about the hypothetical case of delete/truncate without logging at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wodom
wodom
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 256
OK, so from that aspect it looks like DROP is safer (and faster) than TRUNCATE, especially for very large tables. (And being faster makes it safer still.)

Worst-case scenario (in either case, I guess) is restoring from backup. Saying an unlogged operation is more dangerous because the power could fail sounds strange -- the power can ALWAYS fail, and either way, you restore from backup. Unless you're saying that with a logged operation, it can just do a rollback instead? But I don't think you're saying that, since you said TRUNCATE is logged and you implied the allocations, etc. couldn't be rolled back.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87093 Visits: 45267
wodom (10/7/2009)
OK, so from that aspect it looks like DROP is safer (and faster) than TRUNCATE, especially for very large tables. (And being faster makes it safer still.)


They do pretty much the same thing. As far as I know, truncate table deallocates the extents, drop table deallocates the extents and removes the table's metadata. There's no question of safety, both of them will succeed as a whole or fail as a whole (because they are logged operations) and both can be rolled back. On speed, I haven't tested but I'd be surprised if there was a major difference between the two.

Unless you're saying that with a logged operation, it can just do a rollback instead?


Logged operations will always be rolled back/forward upon restart. It's called restart-recovery. SQL reads through the transaction log, finds all transactions that completed but did not have the modified pages written to disk and redoes them, then it finds all the transactions that had started but not committed at the point the server failed and rolls them back.

If you run TRUNCATE within an explicit transaction, it can be rolled back, like any other operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wodom
wodom
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 256
OK, I just looked back at your original post and saw "Edit: Clarified that I was talking about the hypothetical case of delete/truncate without logging at all." So now I think I understand you. In practice, even with a power fail in the middle, you're not going to end up with pages that are marked as allocated but are not allocated, etc. All that stuff would be rolled back and made consistent when you start up again.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87093 Visits: 45267
wodom (10/7/2009)
In practice, even with a power fail in the middle, you're not going to end up with pages that are marked as allocated but are not allocated, etc. All that stuff would be rolled back and made consistent when you start up again.


Absolutely. That's why every operation in SQL is logged to one extent or another, so that there's no chance of leaving the database in an inconsistent state due to an unexpected shutdown.

That's why, if it was possible to delete/drop/truncate without any logging at all (which it's not), it wouldn't be possible to just start over after an an expected shutdown. With the way SQL does changes in memory/disk it would be possible to end up with a huge mess. Pages allocated to two objects, pages marked as allocated but not allocated, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


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