Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    Unanswered: SQL Server 2000 - tempDB problem?

    Hi,

    I believe our problem is related to tempDB on the specific server but I would like to know if anybody has come across a similar issue.

    We have an SQL statement similar to the following.

    BEGIN TRANSACTION

    CREATE #tableA

    INSERT #tableA SELECT (expression A)

    INSERT #tableA SELECT (expression B) WHERE (condition)

    COMMIT TRANSACTION

    First, let me briefly expand on the second INSERT as this may help when reading the points below.

    INSERT #tableA
    SELECT ...
    FROM ... INNER JOIN tableB
    WHERE NOT EXISTS (SELECT ... FROM #tableA WHERE #tableA.columnA = tableB.columnB)

    This script works fine on all of our servers except one, which is why I believe tempDB may be involved. After an analysis of the problem, we have the following results,

    - If we remove the Transaction, the script succeeds.
    - If we leave the Transaction and remove either the first or second INSERT, the script succeeds.

    - If we leave the Transaction and both Insert statements and remove the WHERE (condition) from the second Insert, the script succeeds.

    - If we reduce the row counts from all source tables concerned by 90%, the script still does not succeed.
    - The script had succeeded the week before on the server in question.

    Finally, if we replace #tableA with tableA, the script succeeds.

    Any help on this would be greatly appreciated.

    Thanks.
    Last edited by r123456; 01-29-06 at 17:11.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you say that the script fails, do you mean that the machine spits blue sparks and smoke, or does it do something a bit less dramatic? That answer would distinctly influence my opinion.

    -PatP

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Sorry Pat, what happens when I say 'fail' is indeed important. The script remains running and does not progress past the second INSERT (viewed through Query Profiler). This observation led us to believe locking was responsible, as at one point the query was still running on the same statement after 21 hours, at which stage we terminated it.

    We are quite interested in how the script completed fine a week ago and works today on other servers. We believe something has changed on the server, but not sure what. tempDB has enough space as do the data files and transaction logs.

    Thanks,

    Robert.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Question

    Quote Originally Posted by r123456
    Hi,

    I believe our problem is related to tempDB on the specific server but I would like to know if anybody has come across a similar issue.

    We have an SQL statement similar to the following.

    BEGIN TRANSACTION

    CREATE #tableA

    INSERT #tableA SELECT (expression A)

    INSERT #tableA SELECT (expression B) WHERE (condition)

    COMMIT TRANSACTION

    First, let me briefly expand on the second INSERT as this may help when reading the points below.

    INSERT #tableA
    SELECT ...
    FROM ... INNER JOIN tableB
    WHERE NOT EXISTS (SELECT ... FROM #tableA WHERE #tableA.columnA = tableB.columnB)

    This script works fine on all of our servers except one, which is why I believe tempDB may be involved. After an analysis of the problem, we have the following results,

    - If we remove the Transaction, the script succeeds.
    - If we leave the Transaction and remove either the first or second INSERT, the script succeeds.

    - If we leave the Transaction and both Insert statements and remove the WHERE (condition) from the second Insert, the script succeeds.

    - If we reduce the row counts from all source tables concerned by 90%, the script still does not succeed.
    - The script had succeeded the week before on the server in question.

    Finally, if we replace #tableA with tableA, the script succeeds.

    Any help on this would be greatly appreciated.

    Thanks.
    Why dont u post the exact sql statment and DDL of those tables involved??
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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