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

6 hours for a delete task??? Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 12:48 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:33 PM
Points: 103, Visits: 182
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


  Post Attachments 
Delete.png (6 views, 15.95 KB)
Post #1519379
Posted Tuesday, December 3, 2013 2:16 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 592, Visits: 7,049
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.




-
Post #1519411
Posted Tuesday, December 3, 2013 2:38 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 @ 1:43 PM
Points: 40,411, Visits: 36,861
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 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 #1519417
Posted Wednesday, December 4, 2013 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 14,017, Visits: 28,396
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
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 #1519609
Posted Wednesday, December 4, 2013 12:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:33 PM
Points: 103, Visits: 182
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
Post #1519764
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse