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.