TempDB Full (9002)

Question

TempDB Full (9002)

deucellion

Say Hey Kid

Points: 680
More actions
May 2, 2007 at 2:35 am

#68620

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

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,

Karl
source control for SQL Server

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

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.

EdVassie SSC Guru Points: 60477 More actions · Answer 4

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

deucellion Say Hey Kid Points: 680 More actions · Answer 5

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?

EdVassie SSC Guru Points: 60477 More actions · Answer 6

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

Keith_Evans SSC Enthusiast Points: 150 More actions · Answer 7

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

Keith_Evans SSC Enthusiast Points: 150 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Keith_Evans SSC Enthusiast Points: 150 More actions · Answer 10

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