2

Scenario:

I have an application attempting to write to a SQL Server DB, and I'm getting the message:

The transaction log for database 'DB_NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

However, I see that there is plenty of space on the drive for the transaction log to be written and autogrowth is on. So from SQL Management Studio, I issue:

select name,
    log_reuse_wait,
    log_reuse_wait_desc
  from sys.databases 
  where name = 'DB_NAME'

and can see that the log_reuse_wait is '3'.

Question:

Do SQL Server transaction logs get 'locked' in any way when this wait is in place?

Note that this is set to 'Simple' recover model. Please let me know if there is more information I need to provide to make this a complete question.

  • 1
    How is auto-growth configured on this database's log? Is there a maximum? Is there enough space for the next auto-growth increment? Double-check that the disk the log file is on has the space you're referring to. Also, double-check that auto-shrink is not on. – RDFozz Feb 17 '17 at 22:01
  • 1
    Also: in the Simple recovery model, as soon as a transaction is finished, its log space is released for reuse. If the log file is at its upper limit, then it could fill up while a transaction is running, generate the log full error, roll back the transaction, and then leave plenty of available log space. However, unless you are manually shrinking the log file, or have auto-shrink turned on, the log file would continue to take up as much disk space as it had grown to consume. – RDFozz Feb 17 '17 at 22:16
  • 2
    What version of SQL Server? Have you validated that the autogrowth setting is not only on, but correct? There was a bug where this value would get reset to some astronomical number, and the growth wouldn't be possible because disks simply aren't that big. Also, log_reuse_wait = 3 means "Active backup or restore" - have you checked into that? – Aaron Bertrand Feb 17 '17 at 22:38
  • Do you have any in-memory objects? If you do, one fix is to detach the database, attach it back and quickly, literally within a minute, run a checkpoint and truncate. Do you see any 9002 errors in the application event log or sql errorlog? – stacylaray Feb 18 '17 at 6:31
  • I do not recommend detaching the database - what if you can't attach it again? – Aaron Bertrand Feb 19 '17 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.