Application has a stored procedure that executes a massive delete (millions of records) all at one time which causes a huge transaction log. CANNOT modify stored proc (to break up delete into batches) – vendor restrictions.
SQL Server 2019 / Simple Recovery Model
Does anyone have an alternate approach to manage the transaction log growth when this stored proc runs?
Best Answer
If you cannot in any way modify the stored proc, then it sounds like the vendor is at fault for shipping a stored proc that doesn't work well for their customers. They would need to fix something.
Your options would be things like giving yourself a bigger transaction log that can manage it. If you don't have space you could temporary split the log into 2 files and put it on 2 drives. That comes with risk and benefits such as more IO depending.
Also please look at VLF fragmentation. Your log files are probably heavily fragmented now. Defragging your log files after you find the proper sizing will help performance, recoverability, restores, and some disk space savings.