TempDB Full (9002)
-
Hi All,
In house we have a SQL Server 2000 sp4, (3 node cluster on win2003), 2 nodes of which are used for our companies ERP system.
The problem is that the tempdb occasionally fills up, "9002 (The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.) "
The tempdb is set for auto growth 10% with a size of 300mb in simple recovery mode. The tempdb resides on its own drive, g:, which is part of our emc sans, currently 40gb free space on this drive.
Has anyone got any idea why the tempdb can't auto grow and why we get this error.
Note the problem does occur under heavy usage, month end or year end reporting......
Many Thanks
-
Hi,
The reason you're getting the error is simply because heavy transactional use on the tempdb database is causing the log to grow rapidly and it is either hitting the size threshold or it's filling up the drive (although it's probably that it's hitting the size threshold).
The fact that you've already indicated that the problem occurs during heavy usage provides further evidence that this is the cause.
The reason the tempdb isn't auto-growing is probably because it is reaching its maximum file size, beyond which it won't auto-grow. Just change the value to a higher value.
Hope that helps,
-
Think about this scenario. You allocated 10 MB for your tempdb log file and set 10% for it to grow. Then, a transaction started. It needed 20 MB space in tempdb to process. What would happen?
Allocation of tempdb should be well-planned.
Another possible reason is a large transaction starts to run when the tempdb is shrinking.
-
Allocate more space to the log file. I'd run it up to 2GB or so. It doesn't hurt you to reserve the space and preventing growths during busy transactions is something you want to do.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
You need to work out how much space is needed for tempdb, then set the initial size of tempdb mdf and log to match your business needs. It is also better practice to set growth in terms of MB instead of a percentage. You are more able to predict if the next growth increment can fit on your disk.
If you allow ANY database to grow during normal business processing, you will experience slow performance on that database while it is growing.
Also, SQL will make only 1 attempt at growing a database file to meet the needs of a query. If SQL decides your query will need tempdb to grow by 20 MB, but your growth setting only allows for 10 MB each time, you will get tempdb growing by 10 MB AND a message saying it is out of space!
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
-
Many Thanks for the replies chaps. We have now set the size of tempdb to 2GB and will continue to monitor the situation.
One last question though, when SQL Server is restarted will the tempdb be rebuilt? And will it be rebuilt to a size of 2GB? Will this mean that SQL Server takes longer to restart?
-
My experience is that tempdb goes through some re-initialisation process rather than being totally re-written. We have a tempdb of 50 GB on a DW system and SQL has no noticible delays in starting compared to a system with a 200 MB tempdb.
Without seeing exactly what happens internally, my guess is that the space map pages in tempdb get re-written, and a fresh schema is copied from model, but the remaining data pages are left as they were at closedown. On the log, all logical log files get marked re-usable. This would allow a fresh tempdb to be made available for use, while avoiding the time required to re-format everything.
On SQL 2005 there is a feature called 'instant file initialisation'. If the account running SQL has the 'Volume Maintenance' right in Windows, growing a database or log file to any size you want now takes about 1 second, a vast improvement on SQL 2000!
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
-
I am having a similar issue except that there is near no activity for this installation. I've been watching the tempdb logfile grow 200MB in the past 2 hours with the only activity being my queries against activity and log files (agent and server). The only active processes are mine, Insight manager and WSUS. Mine are the only active processes whereas the others are both idle. Insight has some select locks for a few pids but that's all I'm seeing.
Background: In the past three weeks we started having issues with tempdb log file size growth. The first time this occurred the tempdb log file grew to 55GB (we had autogrow unlimited). After shrinking and changing growth to a 1GB limit, one instance of continuous growth actually shut down the server when it had reached its growth limit and that was during backups.
Any suggestions?
Thanks in advance! Keith
-
Microsoft has a Hotfix post Service Pack 4 that resolves my issue and the continuous growth of tempdb. Please see link below:
Error Described:
http://support.microsoft.com/kb/909369
Cumulative Hot fix pack that contains 909369:
http://support.microsoft.com/kb/916287
Thanks, Keith
-
A 200 mb TempDB is nothing... I wouldn't recommend leaving it that small... as others have stated, figure out how big it needs to be and the set the intial size to that. Actually, I'd set the size to 120% of whatever you figured out it should be.
Just so you don't feel bad about making a 1 or 2 gig TempDB... ours is set to 12 gig to support a little ol' 500 gig main database. We've been able to do some remarkable stuff to resolve some big problems because of it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff. The initial it's set to be 150MB where it's restricted to a growth of 4GB. Before we had set that limit (3-4 weeks ago) we had found it at 55GB and growing where our largest db on this server is 4GB. At that time we only had 10GB of free space left on the drive. Our most expensive operations tempdb space wise are monthly imports from other data sources that weren't scheduled to run.
Thanks again for replying,
Keith
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply