If I'm going to be doing a lot of large deletes from different tables, via an Agent job, and want to keep the Transaction log from exploding, would this work:
Wrap each delete statement in a begin Transaction / commit?
Similiar to this:
DELETE TABLENAME WHERE ID NOT IN (SELECT ID FROM SOMEOTHERTABLE)
Yes, I know the not in is going to make these things take a long time each, the DB was here and poorly created well before I started.
The DB is in our QA, and we're clearing out a large chunk of the records so we can then shrink the QA DB down to free up some space in QA.
I could, but would rather not, put in a DBCC SHRINKFILE('DB_Log', 256) after each delete...
To give an idea: When I did just one delete, the log ballooned from ~700MB to ~25000MB. If it grows by almost that much for each delete, I'll run out of disk space for the logs really fast... Seeing as I've got ~40 tables in this one DB to delete from...