December 3, 2013 at 12:48 pm
For some reson, I have a lot of duplicated records, app. 2.500.000.
I am deleting them with:
delete FROm dbo.DutyRosterShiftHisto WHERE id NOT IN (SELECT MIN(ID)
FROM dbo.DutyRosterShiftHisto GROUP BY Time_Stamp,DutyRosterShiftId,shifttype)
It has been running for 6 hours now.
When I look in the activity monitor, it says 'Suspended' most of the time.
There is one task running all of the time - A select to TempDb, and the delete action is running shortly now and again on the primary database
Should i give up or?
Best regards
Edvard Korsbæk
December 3, 2013 at 2:16 pm
Hm. Deleting 2.5 million records will be quite a bit of work to do; even more so if you have foreign keys that need cascading, or deletion triggers. This will also bloat your transaction log to quite a large degree; if you didn't break the delete up into discrete transactions, it's all going to be one massive transaction.
As far as I know, it's usually best to do deletes in batches; so something like DELETE TOP (10000) FROM (Table) WHERE (Condition), and create a loop to execute this repeatedly until everything meeting the condition is gone.
If you're running in simple recovery mode, add a CHECKPOINT after the deletes, within the loop, so you'll keep the transaction log from bloating. Otherwise, begin and end transactions around the loop (so COMMIT after the delete) for full recovery.
Be careful, though; as always, deletion is something you should definitely test before you proceed, so be certain that looping won't throw off your deletion logic. It looks like you're deleting based on records not existing in another table, so this should be fine, however.
As for your existing transaction, stopping the query in SSMS would be one way of doing it, though it will take quite some time. Running a KILL on the SPID is most likely NOT recommended, because of the problems that could arise from doing so; even if it's going to be quite awhile before the query stops, a KILL should definitely be the last consideration, if it is one at all.
- 😀
December 3, 2013 at 2:38 pm
If you cancel it, the delete has to roll back. That might well take more than 6 hours.
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
December 4, 2013 at 6:49 am
I'd suggest taking a look at sys.dm_exec_requests to see what the process is waiting on. You'll at least understand where the bottleneck lies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2013 at 12:58 pm
The delete proces stopped, when the server ran out of disk place.
Then i made a small program, which every 2.nd minute deleted top (10.000) records.
When i was down on less than 1.000.000 records I changed to delete top (50.000) records.
Its on less than 500.000 now.
Best regards and thanks!
Edvard Korsbæk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply