This question is a follow up on these two:
- Auto rollback Explicit transactions after X amount of time
- Database file …_log has a max file size set to x MB. If it runs out of space, the database will stop working
From the second one I find out that an careless SQL user might leave a very long running transaction and lead to allocating huge amount of disk for the log file.
The first one indicates that killing a transaction after a certain period of time can be achieved through a job, although this approach is not recommended.
If I am sure that none of the transactions opened against a database should not allocate more than a certain amount of log, is there a way to find out how much log is allocated for a SPID, so that can I kill it?
Shortly put, I am interested in capping max log file usage for a transaction.
Question: Is there a way to limit log file usage for a transaction in SQL Server?