Linked serve limitation
-
Is there any limitation on linked server ? I am encountered a problem that push data from one SQL2K5 server to the other (more like to the archive server) and then delete data from source server. Each side of the server has 2 corresponding databases. The process use the same linked server name to do the archive job (with different DB name with the SP). Somehow, it seems if I submit the job in serial it works fine but always fail if submit in parallel ? How can I troubleshoot this problem ?
-
It fails in what way; what is the error?
If multiple processes are simultaneuously selecting and deleting a large number of rows from the same table, there could be blocking involved.
http://blogs.technet.com/b/rob/archive/2008/05/26/detecting-sql-server-2005-blocking.aspx
But blocking generally would just pause one process indefinately until the other process finishes locking the data pages or table. However, the query timeout setting in the database server config or linked server config could potentially cause a blocked remote query to timeout. You may want to confirm that what your remote query timeout settings are set to.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
General Network error. I don't think blocking is the cause as I mentioned earlier, it only failed if running against 2 diff databases at at same time. Blocking seems talking is for the same database unless there is diffferent treatment is use linked serer.
-
BTW, the inked server timed-out is set to 0 (connection time query time out) which suppose means no timed-out
-
John Chiu (3/13/2012)
Is there any limitation on linked server ? I am encountered a problem that push data from one SQL2K5 server to the other (more like to the archive server) and then delete data from source server. Each side of the server has 2 corresponding databases. The process use the same linked server name to do the archive job (with different DB name with the SP). Somehow, it seems if I submit the job in serial it works fine but always fail if submit in parallel ? How can I troubleshoot this problem ?Wouldn't SSIS be a better choice for this kind of requirement?
Linked Servers are not meant for moving huge amounts of data.
One thing you could check is the MSDTC log and search for deadlocks.
-- Gianluca Sartori
-
Application group like 'control' and don't want use SSIS
-
John Chiu (3/13/2012)
Application group like 'control' and don't want use SSISI love it!!!
I always thought that SSIS (or any other 3-rd party ETL tool) gives "Application group" much more control on data movements than pure DB ways of using BCP, linked servers and openrowsets...
:hehe:
Really stupid excuse for not using SSIS.
I use another one: I don't like working with it as it's often a pain in the a*s to change! 😀
-
John Chiu (3/13/2012)
Application group like 'control' and don't want use SSISahhh they are scared of the
clawSSIS!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Kill them! Kill them! Kill them!
😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply