Is there a way to limit log file usage for a transaction in SQL Server?

Question

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?

• LowlyDBA - John M: It might be worth shifting your perspective to look at how can you prevent careless queries from being run in the first place, especially if it is a production environment. Ad hoc queries by users are dangerous, but the downsides be easily nerfed by giving them access to a read-only replica, for example.
• Alexei: @LowlyDBA - yes, that's correct. But it can also be terribly annoying even if this happens on test environments.

Answer 1

If I am sure that none of the transactions opened against a database should not allocate more than a certain amount of log,

The problem of long-running transaction is NOT that it generates too many log records, but in that it prevents the log of being truncated.

So even if there would be the way to limit log records generation per transaction, it could not save you from uncontrolled log growth.

It's enough to write begin tran update myTbl set col =... where key_field = ... that will generate 8-10 log records (depending on server version), but then leave SSMS open and go to a vacation for one week, and if no one kills that open transaction ALL THE LOG generated from that moment will accumulate in a log file independently of recovery model. The log cannot be truncated in this case and it will grow accomodating all the log record generated during the week.

Answer 2

The short answer is no: if a transaction makes changes to the data all those changes have to be written to the log as this is how SQL Server manages atomicity.

The more advanced answer is: it depends on your current database recovery model.

If you have the database set to the simple recovery model then you can reduce log growth by breaking long processes into smaller transactions but this means your process is no longer atomic so you will have to deal with partial updates yourself (SQL Server can't roll back/forward for you or manage what concurrent transactions see). Because in simple recovery SQL Server can immediately reuse the log space used by transactions that have been committed growth is limited by transactions being small. This doesn't work in full or bulk-logged recovery models because the log information for any transaction large or small must be kept until the next log or full backup. See this article for more detail on recovery models and their implications.

• sepupic: This answers on "how can I REDUCE what goes to log", but does not answer on "how o limit log file usage for a transaction". If someone wants to insert 1mln of pages in any recovery model but also wants to limit transactions to write no more than 1Kb of log records and wants this transaction to fail because of violating this limit, I think there is no way to do this in any recovery model