April 14, 2009 at 7:59 am
A stored procedure with transaction to access data from different server using linked server.
The transaction seems never be end.
The I try kill the process for this stored procedure. In Process info, it shows this process status is runnable, and command it Killed/Rollback.
If I kill it again again and again, no change. The message as:
SPID 54: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
How to kill this process forcely?
April 15, 2009 at 4:16 pm
If possible, try dropping the link server and create it again. This might allow the process to terminate faster.
MJ
April 16, 2009 at 6:37 am
Hi,
it seems the process is still running on the linked server. Please try to find the process on the remote server and kill it there
April 16, 2009 at 6:49 am
SELECT spid, kpid, blocked,DBID,DB_NAME(dbid) AS databasename,(SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE))SQLHANDLETEXT,
hostname,loginame,cpu,physical_io,memusage,login_time,
status,program_name,cmd,nt_domain,nt_username
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = 'databasename'
from this query you will find which process blocked....take spid...for example spid is 2 then
goto new query window... and write .........kill spid
that will kill your process....
or you can do one thing....if you don't want to do all this thing then just restart your sql server services ....but don't do this in production...any time...you can do this to only in local server
Raj Acharya
April 16, 2009 at 6:55 am
Instead of trying manythings and getting caught. I guess, do not panic and wait till the process rollback.
Check your rollback is not blocked.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 16, 2009 at 7:07 am
it the rollback takes to long, just stop/start sqlserver.
Also keep in mind, this rollback may cause issues on your linked server (if updated).
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
April 16, 2009 at 8:42 am
Hi,
I've been faced this problem sometimes.
The good news: there isn't rollback process still running. "real rollback"
The bad news: in all cases we needed to restart the MSSQL engine service to clean the processes list.
---------------------
Alex Rosa
http://www.keep-learning.com/blog
April 16, 2009 at 8:48 am
Thanks, guys. I found the solution.
The solution is restart DTC service, not need to restart the engine.
But what I'm confused is: why Begin distributed transaction doesn't work with linked server.
The stored procedure is very simple: just get data(select ) from linked server and insert it into local table.
Nothing else.
Normally, it will be done within less then one second.
April 16, 2009 at 2:07 pm
Kent Zhou (4/16/2009)
Thanks, guys. I found the solution.The solution is restart DTC service, not need to restart the engine.
But what I'm confused is: why Begin distributed transaction doesn't work with linked server.
The stored procedure is very simple: just get data(select ) from linked server and insert it into local table.
Nothing else.
Normally, it will be done within less then one second.
At least that's what you suppose is happening !
What's the isolation level the sproc is being executed with ?
- What volume of data do you expect ? (rows / MB)
- If you post the actual sproc and the ddl of the linked server object (as defined on the linked server !) we may provide more usable replies.
(and some stats regarding the linked object)
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply