Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Will a transaction that is trying to write something into the transaction log be rolled back if the transaction log is full?

share|improve this question

migrated from stackoverflow.com Jan 4 '12 at 17:25

This question came from our site for professional and enthusiast programmers.

Yes.

The log entry is needed to rollback (or server fail etc) so all the changes must be recorded.

share|improve this answer

YES. check this KB http://support.microsoft.com/kb/110139

share|improve this answer
    
That KB article is for SQL Server 6.5, 6.0, and 4.21. It may still be correct and relevant to some degree, but I wouldn't bet any production databases on it. – db2 Jan 4 '12 at 18:52

In sybase you have "abort tran on log full" db_option to choose what to do.

In SQL Server you can't really choose, the rollback is made.

share|improve this answer

Yes but it will be the rollback which actually causes the log to become physically full.

As a transaction generates log records, it will also reserve space in the log for any records required required to rollback. With 20MB of log space remaining and a hypothetical transaction that generates 10MB of log records, you would receive the log full error with 10MB still free and the rollback would then consume that 10MB.

share|improve this answer
    
Nice info on the log space reservation for rollback. – StanleyJohns Jan 5 '12 at 21:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.