January 18, 2012 at 1:13 pm
HI all, i have a strange issue that i can't find any easy answers to.
During and ETL process that runs every five minutes, i occasionally see the following error in the application log: The transaction log for database 'ReportDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Here are some facts.
SQL Server 2008
The Recovery model is SIMPLE.
The log is set to autogrow at 20% with no max size.
There is plenty of disk space (>500gb) available.
There are no locks (according to log_reuse_wait_desc column in sys.databases)
Does anyone have any ideas on why this message would sporatically appear? The only things i can think of are:
1. A bug in SQL Server causing an erroneous error message.
2. The autogrow process is not fast enough to keep up, so the system thinks it is full when it just has not gone through the next growth spurt.
Any ideas would be much appreciated. Thank you!
January 18, 2012 at 1:22 pm
What is the exact build number of your instance?
There are no locks (according to log_reuse_wait_desc column in sys.databases)
This column is not to show locks, it is to show what SQL Server is waiting on in order to reuse space in the log file. If it can't reuse space in the log file that is what will cause it to grow. See here.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 18, 2012 at 1:28 pm
The fact that there's noting listed as the log reuse reason now doesn't mean there was nothing at the time that the error occurred. Maybe set up something to monitor that every minute, catch it during that ETL.
20% is a poor setting for autogrow, especially for the log. The larger the log gets, the larger the growth interval and the longer the growth takes, possibly even timing out. The growth interval should be a fixed size and based on the size of the log and the transaction rate and the speed of the IO subsystem.
My bet: your second thought.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 2:07 pm
Thanks guys. I am not sure of the exact build number right now. The issue and troubleshooting process is greatly complicated by the fact that i do not have direct access to the db and need to put in written questions and queries to the client's dba.
Gilamonster - since the log is set to grow at the large growth interval of 20 percent, is it possible that the error message appears because the log cannot grow fast enough and the system thinks it is full? Also, once the log grows it should stay at that physical size unless a command is issued to shrink the file, correct? if i am correct, it would seem the problem should not happen over and over (i am not sure yet exactly how often the error happens, just want to make sure i am correct about the log file not reducing on it's own.)
many thanks.
January 18, 2012 at 2:16 pm
based on what you said, you must be shrinking the log file too. you should stop that if you are.
auto grow will grow as needed that is true, but it will not release the space unless the database base is shrunk.
January 18, 2012 at 2:19 pm
Could be that the log growth is timing out and the log isn't growing. But you'd have seen errors in the error log if that were happening.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 5:23 pm
right. unfortunately i don't have the error logs right now, but the log from our application, which contains some error messages returned from sql. When i get them from their dba, i'll post what i find.
January 19, 2012 at 1:13 am
Autogrow timeouts won't be in the application log, they will be in the SQL error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply