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 ««1234»»»

delete records in sql without entry in transaction log Expand / Collapse
Author
Message
Posted Monday, October 5, 2009 11:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:21 PM
Points: 33,202, Visits: 15,350
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
Post #798093
Posted Monday, October 5, 2009 11:51 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #798095
Posted Monday, October 5, 2009 3:43 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:23 AM
Points: 134, Visits: 187
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.



Post #798219
Posted Monday, October 5, 2009 5:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 901, Visits: 7,180
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
Post #798247
Posted Monday, October 5, 2009 6:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #798267
Posted Monday, October 5, 2009 11:31 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: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #798324
Posted Wednesday, October 7, 2009 8:49 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:23 AM
Points: 134, Visits: 187
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.



Post #799235
Posted Wednesday, October 7, 2009 9:46 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #799307
Posted Wednesday, October 7, 2009 2:29 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:23 AM
Points: 134, Visits: 187
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.


Post #799524
Posted Wednesday, October 7, 2009 3:23 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: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #799562
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse