One should be cautious when deleting massive number of rows (ex: few millions) from a database. T-SQL Delete command is a logged operation as per the OLTP ACID properties (A for Atomic) which means that all rows we are deleting will be queued up in the database log file (LDF), causing it to expand in size, until all records are processed. Once done, the SQL Engine will commit the transaction, marking that space as available in the LDF file. However, the log file will not be automatically shrunk after the operation. This occurs even if database is in SIMPLE recovery model.
Best way to accomplish this task is to move over records that we don’t want to delete to a table with different name (SELECT * <INTO NEW_TABLE_NAME> FROM <TABLE>) then simply truncate source table (TRUNCATE is a bulk-logged operation) and move back the rows we still need once truncate is done. We will probably need to SET IDENTITY INSERT ON so we can insert identity fields then back OFF once complete.
Also, if need be, remember to disable triggers when doing this. Triggers should not have been in place for starters as a best practice. They are generally evil unless you have one of the very few one-off use cases that require triggers. More on this in another post.
