tempdb log full - (Not really!)
-
I was hoping someone may have run into a similar problem and may have some ideas.
About 1 - 2 times a day, I am receiving the following error from SQL Server:
The log file for database 'tempdb' is full.
Our tempdb log file is currently about 50 MB, and usually about half of it is filled when I go in and look. The file is set to grow by 10%. In addition, there is about 120 GB free on the SAN partition where the log file lives, so it should have plenty of room to grow.
One thought we had is that after a reboot, the tempdb log file is so small, that maybe it has to grow by 10% so many times that is starts stepping on its own toes. Anybody out there think this is plausible? Also, is there a way to set the initial size of tempdb?
Thanks if advance for any help/suggestions you may have.
ajroney
-
I should have included some of the specs
Clustered SQL Server 2000 sp3a
Windows 2003
-
You can use the GUI or ALTER DATABASE command to set the initial size higher.
-
when expantion is done "in process", you may get timeouts to the "expand file" request. SQLServer then says "...file full".
The request itself is not terminated, but is completed after sqlserver received the timeout.
If you can, determine the size youself and alter the filesize using the alter-db statement, so you have control.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ajroney,
There is something I would like to understand.
You told as
"The log file for database 'tempdb' is full."
and
"In addition, there is about 120 GB free on the SAN partition where the log file lives"
My question is: Are you shure the log files of the tempdb is among the logfiles of your "regular" databases? One of the common mistakes is to let the system databases on the regular installation path where they could run out of diskspace.
Bye
Gabor -
When SQL Server wants to expand any file, log or database, the operation can fail if the increment size is smaller than the space SQL needs to complete the active query. When SQL needs extra space to complete an atomic operation, it will try only once to get this space. If the additional space given is smaller than needed, SQL will give a 'file full' message, regardless of how much unused space there is on the disk.
Take TempDB as an example, if your TempDB size is currently 1 MB with a increment of 1 MB, and SQL server needs to store a 10 MB object (e.g. an image) in TempDB.
SQL will start to write the 10 MB to TempDB, and discover it needs to grow TempDB to hold the object. It goes through normal growth processing, and discovers it still does not have enough space to store the 10 MB object. SQL will now give a 'file full' error, and terminate the query.
With a log file, SQL works out how much space will be needed to roll back uncommitted data for your query, and reserves this space in the log. If you insert a lot of data without a commit, you need maybe 3 times the log space for the data you have written to allow for rollback. It is not unknown the space increment that SQL needs when a log is expanded to be smaller than the growth allowed for in the log file definition. Again, SQL will give the 'file full' message.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thank you for your response. Actually after I made the post, a colleague of mine had mentioned just that. We hypothesized that a process needed a certain amount of space, however, the incremental growth was not enough to handle the entire space needed by the process.
So, the quick and dirty is we will set our tempdb log to grow at a higher rate (greater than 10%), and set the intial size to be greater.
We will watch this for a couple of days, and I believe it will resolve our problem. I am curious, however, as to what is happening to the process when tempdb appears to be full because it is not growing at a big enough clip. Is the process just dead in the water?
Thanks again for your responses,
ajroney
-
There are some things in SQL Server that are extremely intolerant of delays. Backups is one and tempdb may be another. Since it is used at a low level by the query processor for worktables, sorting, etc, it may report out of space if it doesn't get a response quickly. In regular databases, when growth occurs, all processing in that db stops as the pages for growth are zeroed out. It can cause some weird delays on the server. I suspect that is what might be the issue.
Thanks for telling us your response and please post a followup if this fixes it.
-
It also sometimes happens that the tempdb gave an error saying that the logfile is full and i was wondering what was happening. When I was running some complex extraction queries on the server, it always gave this message and the process stops after a long time. I was trying to find out what was really wrong, and at the end of every day the hard drive space was becoming a big issue. Then we found out some how there was a database in our server whose log file was not dumped properly and it kept on growing. Some web applications were accessing that database and that database never allowed the tempdb log to grow. So in my experience the tempdb is a database that we need to watch out...
K S Ramachandran
Programmer
Dun & Bradstreet India Limited
ICC Chambers Opp Santogen Mills
Saki Vihar Road
Powai
Mumbai
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply