• 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.

    - 😀