The nightly batch used to finish in twenty minutes. Now it takes an hour, and during that hour the application stutters: every few seconds, every session that writes anything (an order, a login timestamp, a session audit row) freezes for a second or two, then carries on. The SQL hasn't changed. The plans haven't changed. CPU is fine, the storage team says the disks are fine, and the alert log has the same two lines over and over:

Thread 1 cannot allocate new log, sequence 23 Checkpoint not complete

Enter fullscreen mode

Exit fullscreen mode

Nothing is broken, and no query is slow. The database is waiting for itself: the online redo logs are too small for the rate at which the workload changes data , so the log writer keeps running out of logs it is allowed to reuse. This post covers what that message means, why it freezes every writer at once, how to spot it before users do, and how to fix it online.…