Tempdb - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tempdb

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2004-07-27 : 15:37:41

I am running a select query which results alot of record. I got an error message "Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 1
Could not allocate space for object '(SYSTEM table id: -125901408)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full."

I backup the log and shrink the file then i run the query again, and the log file is full before the query completes, i got the same message again.

What should i do to avoid this issue? THanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 15:44:32

Please post your code.

You probably need to fine tune your query and/or add more disk space to support tempdb growth.

Tara

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2004-07-27 : 15:54:42

We have plenty of disk space for the tempdb.
this is the query:
select a.[date],a.[hour],C.MEMBER,(a.[HOURLY USAGE]/b.[MONTHLY USAGE] ) * c.[ACTUAL KWH] as TOTAL_KWH
INTO HOURLY_BY_ESIID_2003_TXU
from LOADPROFILE_ERCOT_2003_MASTER_HOUR a,ERCOT_2003_MASTER_MONTH b,TXU1 c
where month(a.[date]) =b.[month] and month(a.[date]) =month(C.[Assigned Billed Month])

Thanks

shebert
Yak Posting Veteran

85 Posts

Posted - 2004-07-27 : 16:44:44

does this select Give you the results list

select a.[date],
a.[hour],
C.MEMBER,
(a.[HOURLY USAGE]/b.[MONTHLY USAGE] ) * c.[ACTUAL KWH] as TOTAL_KWH

from LOADPROFILE_ERCOT_2003_MASTER_HOUR a join ERCOT_2003_MASTER_MONTH b on a.[date] =b.[month],
join TXU1 c on a.[date]= C.[Assigned Billed Month]

shebert
Yak Posting Veteran

85 Posts

Posted - 2004-07-27 : 16:45:48

has extra , at the end sorry

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2004-07-28 : 11:30:16

shebert,

Your query returns the same result, but again, with a small table , the query runs ok but i guess there're too much data in my table, the query was time out, the log file is full...and i got same error message.
Thanks for your suggestion anyway

Hannah

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-29 : 17:14:02

Have you tried downing the server momentarily to cause tempdb to be destroyed and re-created?

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources