Urgently Need help with an update
-
Here's my problem.
I have report thats comprised of three parts.
There is a table thats built to hold the info.
The developer/dba before built it in three passes, with a load and then two updates.
I have one update script, but not the other.
I am trying to get an update to run by joining the table to another table based on customer id.
Here's the script
UPDATE MEMBERSHIP_MTEST
SET Work_Phone = P.Phone
FROM MEMBERSHIP AM
INNER JOIN Phone P
ON AM.CUSTOMER = P.Customer
WHERE P.Phone_Type ='Off1'
However, when I run this, it basically times out and gives me the following message.
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
Server: Msg 1101, Level 17, State 10, Line 1
I am absolutely baffled.
Its an 8 way server with plenty of ram and space.
This is only for 60K records.
I was trying to go to using a nested cursor within a cursor to read for the office phone then update the phone in the table.
Does anyone have any ideas?
Thanks in advance,
M
-
First off TempDB is used in a lot of operations as a scratch pad so never assume it is not being used by your statement.
From the statement it sounds like either
1) The drive tempDB is on has no further space available to allocate for tempDBs growth and thus no pages are available.
2) TempDB is defined to stop growing at some poitn or automatic growth is turned off on TempDB.
If either are the case and use has caused it to be full it will not be able to allocate a page for scratch work.
-
Try:
UPDATE TEST
SET Work_Phone = P.Phone
FROM MEMBERSHIP_MTEST TEST
INNER JOIN
MEMBERSHIP AM
ON AM.ID = TEST.ID
INNER JOIN Phone P
ON AM.CUSTOMER = P.Customer
WHERE P.Phone_Type ='Off1'
In your previous you were making a CROSS JOIN of MEMBERSHIP_MTEST With the result of the inner join between Membership and customer resulting in the massive amount of records that, if I was your DBA I would have paid you a visit
HTH
* Noel -
Thanks guys
-
I had the exact same problem yesterday... The hd only had 2 mb left of space. Moved a 4 gig db to its own drive and I never had that message again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply