Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstormoen
Starting Member
30 Posts |
 Posted - 2006-05-10 : 15:01:16
|
| I had a process running against the DB that I could not track back to a specific user and using sp_who and sp_who2 it appeared that no cpu or IO's were currently occuring. It was not blocked by any other processes but was blocking many others - After trying everything I could to identify the process I just ended up killing the spid. After I did that it went into killed/rollback mode but if I do kill 72 with statusonly I get the messageSPID 72: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.Everything points to this thing being hung up but I cannot kill it to clear the blocking it is causing. Any ideas ? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 16:20:03
|
| Is it doing an os command?try dbcc inputbuffer and fn_get_sql to see what it's doingor better add this sp and run ithttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlIf the spid is waiting fr something external then it won't stop until the external process dies.e.g. if it is doing a bcp you will have to kill the windows bcp process - that happens if you try to populate a table then bcp it out inside a transaction - the transaction locks the table so the bcp can't access it - the spid can't rollback until the bcp stops so it's stuck.bouncing the server will cure it though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jstormoen
Starting Member
30 Posts |
Posted - 2006-05-10 : 16:25:46
|
| It is doing an insert statement, not sure what is calling it. How can I tell if it was called from and external source ? I did note that there is a kpid but ?Trying to keep from cycling the server |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 16:31:59
|
| the fn_get_sql should give the sql statement for the spid that's calling an external process - see the sp I gave for how to call it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jstormoen
Starting Member
30 Posts |
Posted - 2006-05-10 : 16:42:15
|
| this is the result so sendmail seems to be the trouble but running xp_stopmail errors out.here are the results of the fn_get_sql command. |
|
|
|
jstormoen
Starting Member
30 Posts |
Posted - 2006-05-10 : 16:42:42
|
| oops here are the result.1 341576255 1 0 xp_sendmail |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 17:05:25
|
| Probably the sendmail is trying to include results that it's blocked from getting (or mail is dead).Have you tried killing some of the blocked spids?I rarely manage to get out of mail ploblems without a reboot - it's why I always send mail from a different server dedicated to that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jstormoen
Starting Member
30 Posts |
Posted - 2006-05-10 : 17:47:54
|
| Thanks for your help - it does appear that the rollback is stuck as not IO or CPU have been used for a while and - I have found no way for the mail to stop and start and effectively change the problem spid's status.reboot it is - once again Thanks a ton for that fn_get_sql - If i can't defeat a problem at least I learned some new things in trying. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-10 : 17:53:08
|
| When we had Outlook 2000 installed, we often had to reboot the box or restart the service. MS recommended that we upgrade to Outlook 2003/XP. It has significantly improved the reliability of SQL Mail.Tara Kizeraka tduggan |
|
|
|
|
|
|
Killed SPID still causing troubles ?