October 5, 2009 at 11:31 pm
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
October 7, 2009 at 8:49 am
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.
October 7, 2009 at 9:46 am
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
October 7, 2009 at 2:29 pm
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.
October 7, 2009 at 3:23 pm
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
November 15, 2009 at 8:45 pm
I ran into a similar bulk delete problem recently while trying to delete about 10 million records from a table with about 300 million records. I saw the following using google and I modified it to meet my needs and it worked fine. The code below deletes 500 rows at a time.
set nocount on
set rowcount 500 -- Or whatever your system can handle
set deadlock_priority low
backup log database_name with truncate_only
while 1 = 1
BEGIN
delete from table_name where some_column = some_value
if @@rowcount = 0
break
END
set rowcount 0
Hope this helps with your bulk delete.
November 16, 2009 at 1:07 am
obtllc (11/15/2009)
backup log database_name with truncate_only
Suggesting that with not mention of what it does, what the effects are and what you need to do afterwards? Bad advice.
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
November 16, 2009 at 3:36 am
BOL (2005) Says:
TRUNCATE_ONLY option with BACKUP, Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved however this option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.
November 16, 2009 at 7:58 am
abhijeetv (11/16/2009)
BOL (2005) Says:TRUNCATE_ONLY option with BACKUP, Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved however this option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.
You missed the very important part that ISN'T covered in BOL: it breaks the backup chain and voids future point-in-time restores. Tthis can have devestating consequences for production systems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 16, 2009 at 8:57 am
The SQLGuru has hit this nail on the head. If you have a failure after TRUNCATE ONLY and before your next full backup, you can lose a lot of data.
You don't want to just do this lightly. You're better off just logging things.
November 16, 2009 at 9:04 am
Steve Jones - Editor (11/16/2009)
The SQLGuru has hit this nail on the head. If you have a failure after TRUNCATE ONLY and before your next full backup, you can lose a lot of data.
Which is why, if it is run, it needs to be followed immediately by a full or differential backup to restart the log chain. Without that, all log backups will fail. If no one's monitoring job failures, that can be a nasty surprise if a restore is needed.
What I was complaining about was the lack of info about the breaking of the log chain and the lack of advice on the necessity of a backup afterwards.
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
November 16, 2009 at 9:05 pm
Thanks all for the advice on the use of TRUNCATE_ONLY. I was basically use it to free up space. I have removed it from my code and will be very careful using it from now on.
Once again, thanks.
November 17, 2009 at 2:21 pm
declare @rows int
select @rows = 1
set rowcount 500 -- domain Udt commits
begin tran
while @rows > 0
begin
end
November 17, 2009 at 2:28 pm
The reason range deletes are slow is single log writes, and lots of contect switching. The below example does 1 log commit for 500 rows, and two context switches (runq to sleep q and back). A range delete will do 500 log commits, and 1,000 context switches. On a busy server, where the run q is large, latency also becomes visible (runnable process in sp_who2).
I use the following (with some additional error handling and rollback logic) for table pruning jobs, that run nightly. Been doing it this way for 15 years, it's simple, and never has failed.
declare @rows int, @err int
select @rows = 1
set rowcount 500 -- you can experiment with the rowcount, depends on delete
begin tran
while @rows > 0
begin
delete from <etc>
select @rows = @@rowcount, @err = @@error
commit tran
-- check @err here for non zero --
if @rows >0
begin tran
end
November 18, 2009 at 4:00 pm
To fast delete records with a "where" clause. You will have to do a little work but it is nothing compared the time it takes to delete the records.
Try the following:
1. Script all the primary and foreign key constraints, Identity and indexes etc.
2. Remove all the primary and foreign key constraints, Identity and indexes etc.
3. Delete the records using your where clause.
4. Re-run the script to set the primary and foreign key constraints, Identity and indexes etc.
Thanks
Amarjot
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply