October 8, 2012 at 12:15 pm
Early in the AM we have been getting these messages, usually only for 1 database out of many on the server. We have log backups / shrink every hour and if I check the actual log file size at the time of backup using restore filelistonly disk = 'filepath' with file = position, I see that the log of the so called "full" database is not even near its max log size or near the size of the disk our log files are on. Looking at the log files for all of the databases at that time, the disk was clearly not full. Also, if it were, we be notified of that with more messages. Looking at the backup set, I see that after the log full message, the backup sizes are increasing. This is confusing b/c if the log was full, for example, when the backup size was X, then how is it not still full a few hours later when backup size is 2X or 3X? Basically my question is, are there other reasons that SQL Server will throw this error other than the log file meeting its full size or the log disk getting too full? We're on SQL Server 2012 Enterprise.
Thanks!
October 8, 2012 at 12:24 pm
What is the exact error message you are getting?
Why are you shrinking the logs with every backup?
October 8, 2012 at 12:44 pm
>What is the exact error message you are getting?
Error: 9002, Severity: 17, State: 2.
The transaction log for database 'DBNAME' is full due to 'LOG_BACKUP'.
>Why are you shrinking the logs with every backup?
Good question --- we most likely don't need to shrink with each backup. I know our admin application users have the capability to import lots of data or schedule imports for any time, so perhaps that was the original reason for frequent shrinking. In any case I'm doubtful that this is causing the error above b/c our backup policy has had this operation in it for a long time, but it is a good idea overall to test taking this part out and only shrinking when the log file grows abnormally.
Thanks!
October 8, 2012 at 1:38 pm
I have a suspicion that the log backup isn't the process that's causing the log to be full. Can you check the server error log to see if that message might have been produced by another process that ran before the log dump?
October 8, 2012 at 1:56 pm
Sure --- I found no other errors before that one --- so the log full error was the 1st error in the logs.
Thanks!
October 8, 2012 at 2:05 pm
OK, that's a puzzler. I'm assuming that the log backup is in a script or a maintenance plan. What else is this process tring to do and in what order?
January 21, 2014 at 6:00 pm
We are getting exactly the symptom described in this thread.
We have 500+ databases that on a daily basis we perform full backup and log compress, followed by hourly transaction log backups.
On 4 occasions in the last 20 days we receive 'The transaction log for database 'xxx' is full due to 'LOG_BACKUP'. ' for (random) databases.
Investigation shows that log has not reached maximum size and that there is no shortage of disk space.
Our current theory is that it is related to some sort of an error occurring when the transaction log is expanded (as the result of activity following the compress). Our mitigation is that we are going to be more selective on our log space recovery.
We are running SQL 2012 Enterprise FP5 and W2012 (base)
I have a theory (unproven) that it is related to the Windows rollup patches (sep 2012-current).
Is anyone else in the same boat and experiencing same symptom?
Has anyone installed the rollup patches and had problem go away?
January 22, 2014 at 7:45 pm
Hi John,
We did end up fixing this by doing 2 things:
We turned the log growth up higher, so that the log file is allocated more space during a growth event.
We also stopped shrinking the log files completely unless disk space drops below X percent
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply