asked
Prevent database In-recovery state?
Hi, Recently, we had a CDC enabled production database went into recovery when the Log disk got full. Some big purge transactions were running and cleanup couldn't happen for CDC so the log filled as the marker couldn't move. SQL Server was not able to rollback the transaction, it offline’d the DB then went into recovery. This is not a regular behavior but since it failed to rollback transactions, it shut down the database to prevent inconsistencies and then bought it Online later. What I am looking to know is if there are any available workarounds to not offline the database but to suspend transactions when the log drive gets full ? I am using SQL Server 2008 R2. Date 12/12/2015 06:17:45 Message The transaction log for database '' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. Error: 9002, Severity: 17, State: 4. Date 12/12/2015 06:24:49 Message During undoing of a logged operation in database '', an error occurred at log record ID (12548901:19232:10). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. Error: 3314, Severity: 21, State: 4. Date 12/12/2015 06:24:49 Message The log for database '' is not available. Check the event log for related error messages. Resolve any errors and restart the database. Error: 9001, Severity: 21, State: 5. Date 12/12/2015 06:24:49 Message Database was shutdown due to error 9002 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
I don't know of any default management process to prevent this but someone else here might know of a way. My immediate though is to have some sort of custom process that would monitor the log file space/usage and if it gets within some sort of threshold of running out of space, dynamically stop things or increase the log file space is it's not set to autogrow. Depending on your application this could be deadly or a glorious solution. The better solution in my opinion would be to increase the amount of disk space available to the log file and manage the transaction size appropriately, taking CDC into account. Hopefully the database is living on a SAN where you can easily provision more disk. Even if you don't, see if there's a way to get a larger hard drive in there to accommodate the space needed. If you have large transactions such as the purge, see if you can break those up into smaller transaction to prevent the log file from growing. I definitely would have monitoring setup to watch for when the disk space is getting low. There are a number of vendors (Red Gate, SQL Sentry, Idera,etc) that offer solutions or you can certainly roll your own. Hope that helps.
Nope. No knowledge of anything secret here. Monitor to ensure you have enough space. Run log backups frequently enough to clean things out. Have enough space for surges. Plan everything accordingly.
Thanks John. We do have monitoring in place but the Log disk filled up rather quickly before we could react and took database to 'In-Recovery' state. we have added more storage to the disk as a precautionary step. Moreover it was a regular weekend purge batch but the APP team are investigating on it. Rather this was a question from them and I am checking if such option exists in SQL Server :-)
18 People are following this question.
