Post reply

Query Timeout using a Linked server

  • February 3, 2016 at 6:57 am

    #307687

    To all,

    I have a very strange situation regrading a timeout using a linked server…..

    I wrote me some scripts to restore a PRD database on an ACC and a DWH server based on the backup LSN’s from the production backup history.

    The steps.

    From the ACC server I execute a Stored Procedure on the PRD server (using a linked server on the ACC server) gathering the backup history for one particular database and wrap it into a NVARCHAR and pass it through a second Linked Server (on the PRD server) back to the ACC server.

    This NVARCHAR is pasted into a script (SP on the ACC server) and when completed it is executed and will restore the requested database based upon it’s PRD backup history (Files are on a shared backup environment).

    The scripts for ACC and DWH are identical.

    The ACC and DWH linked servers are identical.

    The SPs for both environments (ACC and DWH) on the PRD server are almost identical (some minor environmental settings – mostly after the actual restore).

    The linked servers op the PRD (to give the NVARCHAR back to ACC and DWH) are identical.

    All linked servers are set up with;

    ‘Connection Timeout’ = 0 and ‘Query Timeout’ = 0

    using a SA account

    All servers are setup with sp_configure ‘Remote Query Timeout(s)’ = 600 (default)

    All SQL servers are 2012 and equal.

    Now the big question:

    When executing the SP on DWH the restore is completed but when executing the SP from ACC the following message is popping up (and the restore remains in NORECOVERY);

    OLE DB provider "SQLNCLI11" for linked server "<<linked server name>>" returned message "Query timeout expired"

    What do I miss ??

    (or who can give me a hint to check some things out I haven’t seen yet??)

    Regards,

    Guus Kramer

    The Netherlands

  • February 3, 2016 at 8:24 am

    #1856311

    Can you query ACC from PRD through linked server?

    Perhaps a firewall issue

  • February 3, 2016 at 10:10 am

    #1856352

    Jo,

    thanks for the reply.....

    Yes - ik can query through the LS and everything seems to be OK but nevertheless I experiance the TIMEOUT (after exactly 30 minutes!!).

    I just found something that might help ( https://support.microsoft.com/en-us/kb/314530 )

    sp_configure 'remote query timeout', 0

    go

    reconfigure with override

    go

    I'm running some tests with it now.....(re-configured this on both target and source server)

    Regards, Guus

  • February 4, 2016 at 2:17 am

    #1856473

    Gents,

    sp_configure 'remote query timeout', 0

    was solving the timeout !!!

    I set it on both target as source server and no timeouts anymore....

    Regards, Guus

  • February 4, 2016 at 2:51 am

    #1856475

    Thanks for posting your solution

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply