Friday, August 17, 2012

Error: 9002, Severity: 17, State: 4

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

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

You will get 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 'DemoData'
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.
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.

1 comment:

  1. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge. SQL Server Training in Chennai