Database file ..._log has a max file size set to x MB. If it runs out of space, the database will stop working

Question

I am using BrentOzar's SQL Server First Responder Kit since I fall under the category "developer who manages Microsoft SQL Servers. It's your fault if they're down or slow."

One of the warning of running sp_blitz says the following:

The [DB] database file DB_log has a max file size set to 10240MB. If it runs out of space, the database will stop working even though there may be drive space available.

Log files are on a separate drive from OS and I indeed limited their growth. All databases have simple recovery model (most of the data is automatically obtained through replication and ETL and recovering from last backup is enough for me).

The database has about 20GB of data.

Question: how can the database stop working if the recovery model does not force me to backup the log?

Answer 1

In simple recovery model, the database still uses the log file. The difference is that this log file doesn't need a log backup to get truncated (to be empty).

A long running transaction may fill that log file and if SQL can't write to it, the database will stop beeing usable.

As Brent recommended, you should let the file growth until it fills the drive (you are on a separate drive so that won't affect the OS) if you don't have a more critical database sharing the same drive.

Answer 2

When someone does a BEGIN TRAN, inserts a row, locks their computer, and goes home for the weekend, your log file will grow and grow and grow.

That would be a Bad Thing™.

Rather than facing an outage, you're better off letting the log file grow, and using your monitoring tool to get alerts that there's a problem.