This is the most common cause of SQL outages and crashes. The SQL transaction logs multiply until they completely fill up the hard disk, causing the databases to go offline and disrupting service to any application depending upon SQL including mission critical applications like SharePoint, Lync, Project Server, etc.

This is preventable by doing one of two things:

1) Change the database recovery option from Full to Simple*

or

2) Create a transaction log backup job. This can be done using the native SQL backup or a 3rd party backup software. To keep the log from filling up again, schedule log backups frequently.

When the recovery mode for a database is set to Full, then a transaction log backup job must be created in addition to backing up the database itself.

Tips:

1. The backup jobs should be monitored because if they fail for any reason, and the logs continue to grow until they fill up the disk, then SQL will crash along with any application that depends upon SQL.

2. The disk where the transaction logs are stored should be large enough to allow for multiple days of failed backup jobs (just in case your DBA is on vacation and the backups happen to fail for that duration of time, and they are not there to respond to the failed jobs).

3. For performance reasons, the transaction logs should be isolated to their own disks because SQL writes logs sequentially, whereas data written to the MDF file is written with random I/O. This is more relevant for Direct Attach Storage (DAS) and less relevant for a Storage Area Network (SAN) where large amounts of cache reside on the disk controller to offset the write transactions.

4. For high availability reasons, the transaction logs should be isolated to their own disks when databases are set to the Full recovery mode because if they reside on the same disk that hosts the MDF database file, and that disk crashes, then your ability to recover is limited to the last full backup. By having the transaction logs on a separate disk, you give yourself to recovery to the point of failure (steps: restore last full backup, then restore the transaction logs).

5. Your SQL backups should not be stored on the same server as the SQL server itself. If the server crashes, how will you get to your backups? Many companies will use a 3rd party backup software to pull the databases backups off the server, and then simultaneously use the native SQL backups to have a 2nd set of backups reside locally. The benefit is you now have two backup copies of your data, and it is usually faster and more reliable to recover data using the native SQL backups than using 3rd party backup software (this scenario is applicable when the server itself has not crashed and you are simply trying to recover data that was deleted).

*Before changing the database recovery option from Full to Simple, you should understand what the difference is between these two modes because you are choosing to only recover to the point of the last full backup. This may be okay for non-critical data, and only the custodian of the data can make that decision.  See this MSDN article for more information.

It is worth noting that backing up the transaction log will not reduce the physical size of the .LDF file (this is a common misconception). The backup will only free up the internal free space inside the LDF file. To reduce the physical size of the LDF file you must shrink the transaction log.  Normally this is not something you

If you do not have a DBA to monitor and respond to SQL backups then consider hiring a company who will monitor this for you.