Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58

    Unhappy Unanswered: Prob in Distributed Query on SQL 2000

    Hi Chaps!!
    I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.

    request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...

    can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)

    thanks in advance to give your time...
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  2. #2
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    for example i am on server1 and running 4 part distributed query for server2 like it

    select * form server2.db2.dbo.table2
    is fine..

    but problem occurs at when this query is with begin statement

    begin tran
    select * form server2.db2.dbo.table2
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is DTC running?

    -PatP

  4. #4
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Originally posted by Pat Phelan
    Is DTC running?

    -PatP

    Yes... even Microsoft msdtcPing test and dtctester is showing success between these two server's
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there anything interesting in either SQL server's error logs? What about the NT logs? Will the "hang" time out in under 35 minutes?

    -PatP

  6. #6
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Originally posted by Pat Phelan
    Is there anything interesting in either SQL server's error logs? What about the NT logs? Will the "hang" time out in under 35 minutes?

    -PatP
    SQL error log show one error occasionaly.. but it is not fixed

    WARNING: EC 685dd538, 0 waited 300 sec. on latch 42c55a30. Not a BUF latch.
    Waiting for type 0x4, current count 0xa, current owning EC/PSS 0x6C711528/0x6C711200.

    There is No blocking for the requested table

    Both server's have the same sql/windows 2000 server and sp versions

    win 2000 server's are on IBM X440 virtual server and for data file storage they are using NAS
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try to profile Server2 looking for connections from Server1. My current guess is that once you engage DTC, it attempts to serialize the row requests instead of caching table2 and that makes the query take so long to run that it seems to hang.

    -PatP

  8. #8
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Originally posted by Pat Phelan
    I'd try to profile Server2 looking for connections from Server1. My current guess is that once you engage DTC, it attempts to serialize the row requests instead of caching table2 and that makes the query take so long to run that it seems to hang.

    -PatP
    But this is happening with all of the tables and objects while accessing data from server2
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by deepakontheweb
    But this is happening with all of the tables and objects while accessing data from server2
    As I expect that it should under my assumption, if those server2 objects are accessed inside of a transaction on server1.

    -PatP

  10. #10
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Originally posted by Pat Phelan
    As I expect that it should under my assumption, if those server2 objects are accessed inside of a transaction on server1.

    -PatP

    YES grammer mistake...
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

Posting Permissions

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