Transaction Log usage by transaction

Is there a way to find out how much of the transaction log a specific transaction uses?

I have a database with simple recovery model and a log file with max size 500 MB. During our tests we ran into a "log file is full" situation, which is pretty strange, because our largest table is just 200 MB in size, we use only implicit transactions, we have no concurrent users and we don't change large amounts of data, apart from the occasional bulk insert which adds another 80 MB or so.

We can probably find the culprit by refining our application tests, but is there a way to find the offending statement through some SQL Server monitoring means?

The log can be forced to grow if you have an old transaction that has not been committed or rolled back, regardless of the size (it could affect a single row). For the database in question, assuming you haven't resolved the problem yet, do this:

DBCC OPENTRAN();

This should show you the oldest active transaction (and there is a good chance this is the one that is preventing the log from wrapping). You can see the last thing they did (but not necessarily the thing that is causing the problem) by (documentation):

DBCC INPUTBUFFER();

And if they have a query that is currently running:

SELECT * 
  FROM sys.dm_exec_requests 
  WHERE session_id = ;

You can also see from sys.databases what the log holdup is (it could be something else entirely):

SELECT log_reuse_wait_desc 
  FROM sys.databases 
  WHERE name = N'';

There can also be reasons why an 80 MB transaction might require more than 200 MB of log space - see Remus' answer here:

• Why does this 120 GB insert grow the log by over 780 GB?

And I recommend you read this page in its entirety (perhaps you are not using the right recovery model, and perhaps you should consider allowing the log more space to grow when it needs to, rather than shutting down all activity until you resolve the situation manually):

• Why Does the Transaction Log Keep Growing or Run Out of Space?

Related:

• Identify cause of unusual transaction log growth

• Contents of transaction log file in more detail

• SQL Server transaction log

• Finding exact statement causing error 3930

• SIMPLE or FULL recovery model for databases?

• SQL Server log file continues to grow?

• Changing a column's data type fills the transaction log

• Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup"

StackExchange Database Administrators Q#61974, answer score: 3