Take the 2-minute tour ×
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.

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.

4 Answers 4

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.