I was executing this very simple query to update a table:
SET table1.OID= tab2.OID
FROM Table1 tab1, Table2 Tab2
WHERE tab1.Commonfield= tab2.Commonfield
I ended having the following message:
Server: Msg 1101, Level 17, State 10, Line 1
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.
The person in charge of the MS sql server, is pretending that when I am executing this query TEMPDB grow by 45 GB due to this query.
I really don't think so.
He made the Tempdb grow to 5 GB and the problem still happening and he shrink it, without any success.
In your opinion:
What do you think the problem is?
To make sure that the problem is resolved, what are things that must be checked or done to resolve the problem?
When you are dealing with large tables like this, sometimes it works best to simplify the problem into two queries. For example, select the ID field(s) from the table you intend to update, first. Then with this temporary table of IDs, execute a separate update.
Or maybe several updates, each processing a block of IDs.
Also be very sure that the common fields are indexed.
Basically what I am describing here is a work-around. You need to "get from point-A to point-B" and there are several ways to do it. Clearly if the log is growing to huge sizes, the server is doing a Cartesian Product join or something very close to it. (Or maybe it's just preparing for a massive roll-back...) Whatever it is, "plan number one isn't working, so try plan number two."
Literally just to get the job done.
I don't have access to the database at the moment (it is at my work place) and today was a holliday.
I do have indexes on both tables and it is the commonfield and I have rebulit them recently to make sure.
How can I run stats, and for which purpose?
Yes - this goes back to my original post about verifying the validity of the fields you are using for your join. Obviously, there is a problem with the CommonField being a many-to-many relationship - which is yielding a product.
Also, I would compare the 2 tables with (to find the repeated CommonField):
select commonfield, (select count(*) from table2 b where a.commonfield = b.commonfield) from table1 a
group by commonfield
having (select count(*) from table2 b where a.commonfield = b.commonfield) > 1