November 23, 2011 at 10:44 am
HI Team,
From application side my user is trying to update the forget password , its immediately blocking in DB side.
Blocking id we are seeing -2 ,
select * from sysprocesses where processid=2
"" LAZYWRITER_SLEEP " is the last wait type ..
Can you update u r suggestions as early as possible .
thanks
November 23, 2011 at 11:32 am
-2 is DTC orphan transaction to kill this ,you have to first find UOW( unit of work) from sys.dm_tran_locks
Select request_owner_guid from sys.dm_tran_locks where request_request_id = -2
Kill 'request_owner_guid'
November 24, 2011 at 2:04 am
HI Seth,
Thanks for the update. Now we are not seeing this error now because we restart the server.
after restart of the server we are seeing the below issue.
.
When the user click on forget username button from application side , immediately the process goes to blocking state in DB .
As per observations at the same time 2 process are running on the same table .
like this
select * from sysprocesses where blocked <> 0
spid kpid blocking
154 4586 189
select * from sysprocesses where spid=
update table set cloumn1 and column2 where coilumn3=@a1 -- 154
update table set cloumn4 and column5 where coilumn3=@a1 -- 189
November 27, 2011 at 2:11 am
HI Seth,
-2 is DTC orphan transaction to kill this ,you have to first find UOW( unit of work) from sys.dm_tran_locks --
why its happening ?? its code related issue or ??
Select request_owner_guid from sys.dm_tran_locks where request_request_id = -2 ---
select * from sysprocesses where blocked <>0 .. when i run this query i am finding -2 in blocking column :
immediately i am running this query ""
Select request_owner_guid from sys.dm_tran_locks where request_request_id = -2 ""
its showing nothing .........
Can u help why this type of issues will come ..
please look into the my previous reply also ....
November 27, 2011 at 2:30 am
HI, when i try to kill the process with status only i am getting below error
[KILL 158 WITH STATUSONLY]
Msg 6120, Level 16, State 1, Line 1
Status report cannot be obtained. Rollback operation for Process ID 158 is not in progress.
November 27, 2011 at 3:29 am
Have you killed the session 158 ? after that only you can fire kill 158 with statusonly to get the roll back status.
November 27, 2011 at 4:37 am
HI ,
I did the same as you mentioned but i am seeing same error .
Kill 154
next
kill 154 WITH STATUSONLY
May i know why these type of errors ???
November 28, 2011 at 6:22 am
Hi ,
Can you please check below link
http://msdn.microsoft.com/en-us/library/ms173730%28v=SQL.90%29.aspx
Seems your rollack completed after you killed.
it says(SQL 2005):
If the rollback of the session ID or UOW has finished when the KILL session ID|UOW WITH STATUSONLY statement is executed, or if no session ID or UOW is being rolled back, KILL session ID|UOW WITH STATUSONLY will return the following error:
"Msg 6120, Level 16, State 1, Line 1"
"Status report cannot be obtained. Rollback operation for Process ID <session ID> is not in progress."
The same status report can be obtained by repeating the same KILL session ID|UOW statement without using the WITH STATUSONLY option; however, we do not recommend doing this. Repeating a KILL session ID statement might terminate a new process if the rollback had finished and the session ID was reassigned to a new task before the new KILL statement is run. Specifying WITH STATUSONLY prevents this from happening.
Best Regards,
Rama Udaya.K
ramaudaya.blogspot.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply