Skip to content

Archive for

Error: 9002, Severity: 17, State: 4

Error

Error: 9002, Severity: 17, State: 4.

The transaction log for database ‘MyDB’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

CAUSE

Your database properties is set to auto-shrink and log file is set to some restricted value.

You will hit this error when you are trying to shrink/auto_shrink starts for database log file but it didn’t work because of some active transactions. Please note, active part of the log file cannot be shrunk and can cause delay in shrinking process.┬áIn order to identify the exact reason as what is causing this issue, run below select query: –

select log_reuse_wait,log_reuse_wait_desc from sys.databases where name like 'MyDB'

If log_reuse_wait is ‘0’ that mean you are good to shrink the file now. You can see the short description of each log_reuse_wait value against the log_reuse_wait_desc field. For detailed on log_reuse_wait values visit here.

RESOLUTION

Here your database needs more log space so increases the log space or set the maximum limit of log file to some higher value/unlimited (make sure auto growth of database log is is enabled). Also, it is recommended to disable auto_shrink as keeping it enabled is not a good option.