June 5, 2009 at 12:13 pm
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?
June 5, 2009 at 12:52 pm
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
June 5, 2009 at 1:17 pm
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
June 5, 2009 at 9:12 pm
I agree with Vinay... sounds like someone set the growth limit on TempDB.
--Jeff Moden
June 7, 2009 at 1:39 pm
the db is set to unrestricted growth and has been, so that's not it.
June 7, 2009 at 2:42 pm
What process/procedure is causing the error to be generated?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 7, 2009 at 2:55 pm
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
June 8, 2009 at 1:30 am
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
June 8, 2009 at 4:25 am
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
June 10, 2009 at 2:49 am
Hi, has the SQL service system user rights to your hard drive?
June 10, 2009 at 4:06 am
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?
June 10, 2009 at 4:51 am
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.
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
June 10, 2009 at 5:34 am
Thanks ALZDBA for confimation , this is what i wish to state that this is a normal trend..
🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply