December 28, 2007 at 3:15 am
Hi,
Have a problem with linked server query and openquery - when executing query
against linked server, on irregular time span, there is a number of spid's
hanging with no possibility to kill process. When issuing kill, spid hangs in
killing/rollback state indefinitely, cousing sometimes tempdb log to grow
constantly.
Only solution found so far is server restart :S, which is causing serious
business problems...so, any help is appreciated.
Both servers are SQL 2005 (9.0.3175)
Sinisa
December 28, 2007 at 5:41 am
- Solution 1: get rid of the linked servers ! 😀 Maybe the process can be performed asynchrone, so replaces by a sqlagent job or so. You might even consider to start the job (using alerts) from within your sproc or application.
- As you can suffer, the availability of the linked server and it's data can hurt the performance of your "primary" server.
If you perform updates at the linked server side during a kill of the spid at the primary server side, data at linked server side also needs to be rollbacked.
- Depending on the liked server query, sqlserver will determine which part of the query it will present at the linked server, pull over the data (tempdb) and perform the rest of the query. (hence the growth of tempdb)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 28, 2007 at 9:50 am
I've had linked servers flake like this in 2000, but haven't seen it in 2005 (much less experience also).
What type of server is this? I've seen issues with DB2 where their driver leaked memory and died and we couldn't fix it without a reboot.
January 11, 2008 at 1:28 am
Sorry,
my mistake - other one is 2K SP4 with additional hotfixes.
Still have same behavior from time to time. I found that other people have same problem, even there is a bug submit on MS concerning this - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=187192
rgds
Sinisa
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply