Wednesday, May 22, 2013

Autogrow of file 'DB_log' in database was cancelled by user or timed out after xxxxx milliseconds

Recently a production SQL Server caught my attention. Database users complained that they got an application exception "This SqlTransaction has completed; it is no longer usable." And more and more people got thie exceptions and during peak time no one actually was unable to make any transaction.

When I checked SQL Server Error log, the following exceptions kept occurred every 30 secs.
Autogrow of file 'DB1_log' in database 'DB1' was cancelled by user or timed out after 18422 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

As the error message suggested, I tried to reduce FILEGROWTH from 200MB to 100MB.
But it did not make big difference. The next day people complained again in peak time. So I checked the DB log space.
DBCC SQLPERF(logspace)
This command displays current log size and its usage percentage for all databases.
It turned out that the database 'DB1' was 95GB big and 99.9% used. And it eventually reached 100%...

Why did SQL Server fail to increase log file even if there are enough big disk space?
Well, do not know exactly. But since this was production server (and I am not a DBA for the database servers :-)), it has to come to usual state. As you know, primarily this is a DBA job, which maintains transaction log properly. And on a second thought, this might be a SQL Server bug (Note: SQL Server version was SQL 2008 v10.0.5768).

I ended up restarting SQL Server service since no one actually can make single transaction. And changed database recovery model from Full Recovery to Simple Recovery, which immediately release all log spaces. Sure, this is not always good choice for everyone. Changing to Simple Recovery means that it can lose the data since last backup if any bad thing happens to the database. Fortunately the database was in RAID and also was allowed to take a risk to lose 1 day data. If you have finantial data in your database, sure, you can not take this approach.

In SQL 2008, BACKUP LOG DB1 WITH NO_LOG (or WITH TRUNCATE_ONLY) cannot be used. Instead, one has to switch recovery mode to Simple and then the log will be truncated. After log truncation, DB backup is normal required to be safe. And one can reconsider to change recovery mode back to Full recovery.