log file for database 'tempdb' is full

[Senthuran]

Hi

I am getting this common error once or twice a day:

Error: 9002, Severity: 17, State: 2
The log file for database 'tempdb' is full. Back up the transaction
log for the database to free up some log space.

provided......

1. My log file drive has more than 20 GB free out of 30 GB
2. Both data file & log file has default setting on unrestricted file
growth by 10%
3. Currently we moved from SQL 7.0 to SQL 2000 & the load in the user
side also doubled
4. We can't do the temporary solution like restarting the server or
SQL service, because the application is a real time system with much
less manual interaction.

Thanks in advance.

Regards
Seni

[Erland Sommarskog]

Senthuran (se******@yahoo.com) writes:

Error: 9002, Severity: 17, State: 2
The log file for database 'tempdb' is full. Back up the transaction
log for the database to free up some log space.

provided......

1. My log file drive has more than 20 GB free out of 30 GB

It appears that you have some operations that take a serious load in
tempdb. Could be worktables, could be temptables that grow a lot,
and which see a lot of updates. I am afraid that you need to track
down which operations this might be.

When you say that there is 20 GB free, is this just when you have
gotten this message, of after you have restarted SQL Server?
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp