I have a long running query which is a fairly basic update statement to about 10 million rows of a 50 million row table. (its taking the min of the identity PK grouping by tel and applying it to rows with a higher identity column value but the same tel).
I realise the hardware is heavily under spec - 2gb ram, 2 2.8 xeon, 2 scsi 10k 70GB disks mirrored for data, same for logs. This query ran for 44 hours before i decided it might take 10 days - the process is blocking itself and it gets cxpacket when it tries to multithread etc!
It now has 18 hours left to rollback 🙁 I can recreate the entire 50 million table in under 30 mins. Is there anyway for me to a) stop the rollback b) force a truncate on the table c) restart the server with the database in some not ready state that will let me do something.
Presently my most time efficient choice is; stop the service, delete/rename the database, restart the service, restore a backup (6 hours).
Any suggestions?