The database locks up when the transaction log gets full.
The good news is this is SQL Server, where transaction log cleanup is simple. Just manually backup and truncate the transaction log. Oracle is much less forgiving.
Going forward, limit the size of the transaction log to be less than the available disk space.
This provides a buffer in case the lack of disk space prevents transaction log cleanup.
Are you using the correct recovery mode?
The recovery mode for SQL Server determines how the transaction log is used.
Full
Full recovery mode allows the transaction log to keep growing until it eventually consumes the disk.
It allows you to restore the database to a specific point in time.
Use full recovery mode with systems where losing large time frames of work is not acceptable (assuming full/differential backups).
Limit the size of the transaction log file and define a maintenance plan to backup and truncate the transaction log periodically during the day.
The point is to always back it up before it runs out of space.
Simple
Simple recovery mode grows the transaction log for the current transaction. When the transaction completes, it is dropped from the log.
It only allows recovery to the last full backup.
Use Simple recovery mode in systems where losing a day's effort isn't an issue (e.g. test/dev).
Limit the size of the transaction log file, but make it large enough that you don't need to back it.
Simple mode only supports full backups. If you misjudge the size requirements, you will need to extend the transaction log until the current transaction completes.
