Sql-server – Transaction Log usage by transaction

sql serversql-server-2008-r2

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?

Best Answer

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(<spid>);

And if they have a query that is currently running:

SELECT * 
  FROM sys.dm_exec_requests 
  WHERE session_id = <spid>;

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'<database name>';

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

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):

Related:

Related Question