March 13, 2012 at 8:06 am
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 ?
March 13, 2012 at 8:24 am
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
March 13, 2012 at 8:41 am
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.
March 13, 2012 at 8:44 am
BTW, the inked server timed-out is set to 0 (connection time query time out) which suppose means no timed-out
March 13, 2012 at 8:53 am
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
March 13, 2012 at 9:44 am
Application group like 'control' and don't want use SSIS
March 13, 2012 at 10:02 am
John Chiu (3/13/2012)
Application group like 'control' and don't want use SSIS
I 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! 😀
March 13, 2012 at 10:40 am
John Chiu (3/13/2012)
Application group like 'control' and don't want use SSIS
ahhh they are scared of the claw SSIS!
Lowell
March 13, 2012 at 11:11 am
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