need help -- tempdb full message, but its only 8 mb in size

Question

need help -- tempdb full message, but its only 8 mb in size

SQL_NuB

Hall of Fame

Points: 3666
More actions
June 5, 2009 at 12:13 pm

#155789

On my SQL 05 server, I'm getting this error in my event viewer and none of my apps that connect to the databases on this server are able to:
error:
Unknown SQL Exception 9002 occured. Additional error information from SQL Server is included below.
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
the temp db is 8,192 and the log file is 768 kb and I have 400 gig free on my drive.
I stopped and started SQL serveral times, but I keep getting this error.
Whatelse can I look for and do to resolve this issue?

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 1

I'm able to access all non sharepoint databases on the SQL 2005 server, so its something sharepoint related, I'm off to the sharepoint world, which is like trying to find a needle in a haystack

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 2

Have you ristrict the growth of tempdb. check property of data and log files.

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

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

I agree with Vinay... sounds like someone set the growth limit on TempDB.

--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)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 4

the db is set to unrestricted growth and has been, so that's not it.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

What process/procedure is causing the error to be generated?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

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

Mike (6/7/2009)
the db is set to unrestricted growth and has been, so that's not it.

Stupid question, then. Are you sure you're looking at the correct server? TempDB full messages only occur when it's full and trying to go beyond a set limit OR the app user has absolutely no privs on TempDB.

--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)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 7

This kind of message also occurs if sqlserver cannot take the extend of a db file with a timeout periode.

The extend will still be taken by the os, but sqlserver will raise the error and will not wait for the full completion for that thread.

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

EdVassie SSC Guru Points: 60476 More actions · Answer 8

Alzdba has given one case of where you will get a DB full message when you have lots of disk space. There is another...

SQL Server will only make one attempt to grow a database if a given query needs more space than is available. If the object you are trying to store is larger than the growth increment, SQL Server will do the DB growth then discover it still does not have enough space and give the error message.

For example, if you are trying to store (say) a 5 MB object in a 2MB database that can grow by 10% your insert will fail, regardless of the amount of free space you have on disk. If you repeat the insert enough times it will eventually work, because the failed inserts will each have grown the database a small amount until eventually there is enough fre space to accept your insert request.

It is important to get the size and growth amount of your tempdb right for your application. There are a lot of posts about how to do this - look for posts about using multiple fixed-size files for tempdb.

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

manolomalaga SSCommitted Points: 1697 More actions · Answer 9

Hi, has the SQL service system user rights to your hard drive?

ankur_libra Hall of Fame Points: 3999 More actions · Answer 10

Hi,

I have also faced the same situation where the same message was logged in error log on one of server. The Tempdb Autogrow settings has been enabled and set to unrestricted growth. Even then this Error 9002 was logged.Moreover enough free space was there on server disk.

But on checking it was found that nothing abnormal happened. i.e Tempdb autogrows.This means that tempdb database was autogrown to the required size as per the need, after logging this message.

Did any user/process faced any other problem when it was logged?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 11

That's normal.

SQLServer will only wait for its standard timeout value of time.

The OS will still complete the extention after sqlserver has raised its error.