| Author |
Topic |
|
ServerTeam
Yak Posting Veteran
83 Posts |
 Posted - 2008-05-08 : 12:11:00
|
| can we just kill spid and drop it without doing any rollback ...?Thanks,ServerTeam |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-08 : 12:25:35
|
| Depends on the transaction. Rollback occurs for long transactions if you kill it.You can check status with:1) DBCC opentran for uncommitted tran 2)sp_who2 to see if rollback3) Kill Spid with statusonly - to see status of rollback tran. |
 |
|
|
ServerTeam
Yak Posting Veteran
83 Posts |
Posted - 2008-05-08 : 12:30:06
|
SPID 139: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 325 seconds. every time it shwing the same estimation time.This is a long transaction.Is there any way kill this.:-(quote:
Originally posted by sodeep
Depends on the transaction. Rollback occurs for long transactions if you kill it.You can check status with:1) DBCC opentran for uncommitted tran 2)sp_who2 to see if rollback3) Kill Spid with statusonly - to see status of rollback tran.
Thanks,ServerTeam |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-08 : 12:35:31
|
| Let it finish the rollback. Any reason for killing? |
|
|
|
ServerTeam
Yak Posting Veteran
83 Posts |
Posted - 2008-05-08 : 12:41:04
|
| yes will wait till rollback has done completely..:)As BCP process is running..thru this spid..as this is blocking just killed the process .But still its blocking while rolback.Just want to get information is there any process to kill any spid without rollback if this is the case..Thanks,ServerTeam |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-08 : 12:50:39
|
| No way. What status do you see now? |
|
|
|
ServerTeam
Yak Posting Veteran
83 Posts |
Posted - 2008-05-08 : 12:54:15
|
| As RUNNABLE AND COMMAND AS RUNNABLEThanks,ServerTeam |
|
|
|
ServerTeam
Yak Posting Veteran
83 Posts |
Posted - 2008-05-08 : 12:55:31
|
| as RUNNABLE and command as KILLED\ROLLBACKThanks,ServerTeam |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-08 : 22:48:18
|
| >> can we just kill spid and drop it without doing any rollback Possible by restarting sql with specific trace flag. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-09 : 01:22:21
|
quote:
Originally posted by sodeep
quote:
Originally posted by rmiao
>> can we just kill spid and drop it without doing any rollback Possible by restarting sql with specific trace flag.
Not sure, it might keep your database in inconsistent state.I might be wrong .Never tried.
|
|
|
|
|
KILL spid