Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


6 hours for a delete task???


6 hours for a delete task???

Author
Message
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 296
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
Attachments
Delete.png (6 views, 15.00 KB)
Andrew Kernodle
Andrew Kernodle
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 8135
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.

- :-D
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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

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


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17586 Visits: 32264
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 296
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
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