Results 1 to 15 of 15

Thread: TEMPDB Full

  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: TEMPDB Full

    I was executing this very simple query to update a table:

    UPDATE Table1
    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?

    Your help is highly appreciated.

    Thanks in advance

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    How big are these tables and are you joining correctly ? Convert the update to a select and see how large the resultset is - you might have a product.

  3. #3
    Join Date
    Oct 2003
    Posts
    9
    The 2 tables are respectevly: 100000 rows and 200000 rows
    The rwo tables are well joined, no doubt about that

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Wink

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Nour
    The 2 tables are respectevly: 100000 rows and 200000 rows
    The rwo tables are well joined, no doubt about that
    That's not a lot of rows...

    The query should be written like

    Code:
    UPDATE T1
       SET OID= T2.OID
      FROM Table1 T1, Table2 T2 
     WHERE T1.Commonfield= T2.Commonfield
    Can you post the DDL of the 2 tables?

    If "Commonfield" are not the keys to the tables, you'll end up with a cartesian product...

    Now that would be a lot of rows....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Convert the update to a select statement - either select count(*) or select 1 column and stop the query if it runs too long in qa to see how many records are returned.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Run this and let us know

    Code:
    DECLARE @x datetime, @y datetime
    
    SELECT @x = GetDate()
     SELECT COUNT(*)
      FROM Table1 T1, Table2 T2 
     WHERE T1.Commonfield= T2.Commonfield
    SELECT @y = GetDate()
    SELECT DATEDIFF(ss,@x,@y) AS Seconds_To_Run
    I would say your count should be 200,000 or less....if it's more, you have a problem.

    But the DDL would really be useful here....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Oct 2003
    Posts
    9
    The commonfield is a PK in one table and FK in another, the problem is I have run this query tens of times and it execute perfectly in few seconds or minutes.
    This si the first time is happening.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you run this?

    USE Northwind
    GO
    DBCC CHECKTABLE ('Orders')
    GO


    Also may be the index needs to be rebuilt or stats need to be run...

    Is the key a Unique Clustered Index?

    Did you run the other code I posted?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2003
    Posts
    9
    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?
    Last edited by Nour; 11-11-03 at 21:18.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    Have you run the select version of your query ?

    The stats is part of the equation that the optimizer uses to determine the execution plan. Have you looked at the execution plan in query analyzer ?

    You can run sp_updatestats but this should not be necessary unless your configuration is not setup for this (the default is to auto update).

  12. #12
    Join Date
    Oct 2003
    Posts
    9

    For Brett Kaiser

    I have run the query and I got this:

    Seconds_To_Run
    --------------
    52

  13. #13
    Join Date
    Oct 2003
    Posts
    9

    For Brett

    Hi Brett

    The count is 325448

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you join a table with 100,000 rows to a to a table with 200,000 rows

    How can you get 325,000 rows?

    Unless there's a problem with the predicates?

    Can you post the DDL of the tables?

    Include the PK's anf FK's

    Does it still take a minute to run?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2002
    Posts
    2,232
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •