0

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?

jay
  • 21
  • 7
  • Quite simply - size the transaction log according to the usage and just leave it alone. Stop worrying about it because you can't manage what you can't control. If you are trying to solve a problem (big tlog is not an actual problem), then you need to work within the constraints of your license - consult your vendor / support group. – SMor Jan 12 '21 at 17:16
  • if you are running db in simple recovery - point-in-time restore is not possible. This infers that the db is not that critical and some downtime is acceptable. If so, how about u create a shadow table in different schema, dump the data from main table to shadow table and then just switch schema. see my answer https://dba.stackexchange.com/a/138765/8783 – Kin Shah Jan 12 '21 at 17:59
  • Not a t-log tip, but to speed it up you can disable row and page locks beforehand to force a tablock, then re-enable them after. – Charlieface Jan 12 '21 at 21:40

2 Answers2

0

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.

Ali Razeghi - AWS
  • 7,518
  • 1
  • 25
  • 38
  • I appreciate your input , especially on the VLF fragmentation (which was an issue earlier ). Vendor knows about this problem and isn't budging so I am left with adding storage. . – jay Jan 12 '21 at 19:24
0

Massive delete - by that you mean stored procedure is deleting ALL rows in a given table ?

If yes, and if there are some patterns in its schedule (you know when it happens), you could create a SQL Server job that would truncate the table right before app/procedure deletes the rows... this way you will avoid t-log growth

If no (only a subset of rows in a table is deleted, although millions of records at a time) - I don't see any alternatives other than to increase the volume/drive's size where transaction log resides, which will allow to avoid "transaction log full" error

But in the end, best solution would be to work with vendor and have them optimize the procedure and implement batching

Aleksey Vitsko
  • 5,589
  • 5
  • 34
  • 64