3

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?

Improve this question

1 Answer 1

Reset to default
3

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:

Improve this answer
2
  • Thanks for the answer! I checked for old active transactions already but there weren't any. As I mentioned, we don't have concurrent users, we just run a complicated business process once in a while to process some data. I'll look into the related links though, thanks again.
    – TToni
    Commented Mar 28, 2014 at 15:41
  • I also wondered if there is some statistics/profiling setting which I can enable before I execute a command which will tell me the space used in the logfile.
    – eckes
    Commented Mar 6, 2017 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.