Database enters in recovery mode every time the Transaction Log is full

First of all few housekeeping rule.

• You (or your DBA) should manage transaction log space depending on your recovery model.
• Do not let transaction log to get full and impact your database/application availability.

Following two links can help you better manage transaction log file.

• SQL Server Transaction Log Architecture and Management Guide
• How do you clear the SQL Server transaction log? Answered by Aaron Bertrand which also listed some great resources around transaction log issues.

What you are experiencing is not normal behavior when transaction log file is full and cannot grow further.

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING. In either case, user action is required to make log space available.

The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill. To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.

What you are seeing is failure in transaction rollback. For more details read this post.

• INF:What happens to a database when transaction rollback fails?

As per Paul Randal's blog post you hit a bug which was fixed in SQL 2012 SP4.

• 2012/2014 bug that can cause database or server to go offline

More details on error 3314:

• Troubleshooting Error 3313, 3314, 3414, or 3456 (SQL Server)

Reference:

• Troubleshoot a Full Transaction Log (SQL Server Error 9002)

The first and the foremost thing that your database is already in Simple recovery mode so log will not grow much until it is held up by a single transaction. Once the checkpoint comes the log will be truncated itself. Now that you have limited the log size, SQL Server goes for a toss when the transaction is in the middle and there is no scope of log to grow. Simply kept, the transaction needs the log to grow. You cannot make the transactions complete without allowing the logs to grow when required. This is why the best setting is to keep the AUTOGROWTH enabled.

SQL Server treats it as a crash and then goes for a recovery to rollback the incomplete commands as seen in the error log.

Solution : Enable the AUTOGROWTH. Also if this is an important database then switch to Full recovery Mode and configure log backups.

This is my take on your issue :

Microsoft SQL Server 2012 (SP1) - 11.0.3156.0

This is pretty old build and lot of fixes have been put into newer SPs. You should patch your server with SP4 atleast - 11.00.7001.

Error: 845. Severity: 17. State: 1.

This is your disk subsystem causing issues. Check your \MSSQL\LOG\sqldump folder. You will have stack dumps created. You can analyze it as per SQL Server Latch & Debugging latch time out or open a case with Microsoft. Again, check your disk subsystem.

Error: 5901. Severity: 16. State: 1

This was fixed in Cumulative Update 8 for SQL Server 2012 SP2

so takeaways:

• Patch with SP4 and see if you encounter issues.
• Check with your windows or storage admin for disk contention.
• Make sure you have enough RAM and Max memory is set appropriately.
• CPU power plan is set to high performance as opposed to balanced.
• Enable autogrowth to fixed MB so that the db can grow appropriately.
• Run sp_Blitz to identify potential issues.