December 15, 2010 at 5:16 am
My customer told me that he is facing a slow performance and when i executed the query "select * from sysprocesses where blocked <> 0 " it was showing a row with spid 101 and waittime is about 17528000 and i killed the SPID (kill 101) by getting permission from customer but still it was showing in the result set wat should i do to get rid of that?
December 15, 2010 at 5:24 am
Sounds like its doing a rollback...
Perhaps next time, find out what its doing and why its taking so long before you kill it off.
December 15, 2010 at 5:32 am
Check this spid in using sp_who2 proc,and check whether its wait for other request id(may be deadlock condition)
December 15, 2010 at 5:32 am
premkuttan.lakshmanan (12/15/2010)
wat should i do to get rid of that?
Wait. It was doing some large data modification and has to roll back all of the changes. Rollback usually takes as long or longer than the operation to the point you killed it.
Do not try to restart the sQL service. If you do, the rollback will continue after the restart and the database will be offline for the duration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2010 at 5:33 am
Developer 2005 (12/15/2010)
(may be deadlock condition)
If it was a deadlock SQL would have detected and handled it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2010 at 5:58 am
in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled back
December 15, 2010 at 6:10 am
Yes. The connection has been killed and SQL is rolling it back. Be patient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2010 at 6:48 am
GilaMonster (12/15/2010)
Yes. The connection has been killed and SQL is rolling it back. Be patient.
Gail, as a matter of interest, will DBCC inputbuffer on the SPID work now to show what statement was causing all the grief? I normally run that before killing the SPID 😛
December 15, 2010 at 6:51 am
Offhand, no idea. The DMVs should. sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2010 at 8:10 am
premkuttan.lakshmanan (12/15/2010)
in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled back
If a spid such as 101 shows that it is blocking itself, that is not really a problem.
What if 101 was doing an important update ?
DBCC inputbuffer will often show what is being executed
December 15, 2010 at 8:17 am
homebrew01 (12/15/2010)
premkuttan.lakshmanan (12/15/2010)
in sp_who2 the coressponding blkby for spid 101 is 101 and in the corresponding command column it shows as killed/Rollback . Does it mean that the activity is getting rolled backIf a spid such as 101 shows that it is blocking itself, that is not really a problem.
What if 101 was doing an important update ?
Hopefully it wasn't doing the December payroll and bonus run. :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply