February 24, 2011 at 4:59 am
Hi All,
Working on SQL Server 2008 R2. There was an UPDATE statement running on one of our dbs that was causing blockings.One of the guys killed the process. It's currently in a ROLLBACK state. My concern is that it's currently not doing anything. I say this because when I check sys.dm_exec_requests for that process, under COMMAND it says AWAITING COMMAND. Also if I execute KILL SPID with statusonly it shows 0%. The wait type for this process is XACTLOCKINFO...something I've not seen before. This statement has been in ROLLBACK now for the past 13 hours.
If I run dbcc opentran on the database the oldest active transaction is not the spid that was killed.
Any help will be greatly apprecited.
Thanks
Denesh
February 24, 2011 at 9:05 am
have you run sp_blocking or look in blocked by of sysprocesses
to see if someone is blocking it
February 24, 2011 at 12:45 pm
Wow. After 13 hours, the kill status is still at 0%? Either the statement was running for a very long time with tons of data updates BEFORE it was killed, or there is a problem.
It is normal for the rollback to take about the same time as the query was running before it was killed.
If you don't find any blocking, then I would diagnose this as a typical performance issue - perfmon or your favorite tool to locate the bottleneck. This is sounding strange enough to me that the problem may be a service pack issue. You may want to do a select @@version and see what hot fixed you are behind on and see if any future fixes address anything related to this.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 24, 2011 at 1:03 pm
There is only one way of getting rid of a killed/rollback spid that wont go and that is to restart the service.
I've had this once before and found the only information I could get from Microsoft was there was a problem in the service itself and I needed to restart the service during the next downtime slot.
February 24, 2011 at 2:56 pm
MysteryJimbo (2/24/2011)
There is only one way of getting rid of a killed/rollback spid that wont go and that is to restart the service.I've had this once before and found the only information I could get from Microsoft was there was a problem in the service itself and I needed to restart the service during the next downtime slot.
I have seen this also a couple of times - and the only way to clear it was to restart the service. However, before you go that route make sure you have exhausted every other possibility.
And, before you restart the service make sure you have good backups of all databases and especially the database where this process is running. Just in case...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2011 at 10:27 pm
Hi All,
Thanks for the responses.
We have confirmed that the process in ROLLBACK is not being blocked.
Yes 13 hours is a long time however the process was running for 15 hours before being killed...however it's now gone more than 24 hours and that process is still in ROLLBACK.
What I did also find out was before the query was KILLed...it was also showing AWAITNG COMMAND. Also the query text that it was executing at that moment was empty/blank.
We are planning a restart tomorrow (after a successful FULL backup)
One of the things I'm still trying to investigate is the current Wait Type which is XACTLOCKINFO. Something that I found on the internet says 'Awaiting maintenence on list of locks"
Has anyone see this wait type before and can shed some more information on it?
Thanks again
Denesh
February 24, 2011 at 10:41 pm
Yes, restart service is the way to fix. Most interesting to me is that, I experieced this a few times on SQL2000 but not other versions above that (yet). I was thinking it must be a SQL2000 thing but now it approved it's not. Not sure if this means anything to Microsoft or anyone out there can connect back to Microsoft.
February 25, 2011 at 12:16 am
Yes it is true restart engine is good solution for this...Because sql server is trying to rollback transaction
February 25, 2011 at 8:02 am
Ya, I kinda figured it was a problem requiring a recycle and not standard behavior with a monster query. Bummer.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 25, 2011 at 8:08 am
yes its normal because if you write begin tran or not sql server engine start a transaction when you send update or insert query it if it is finish
sql engine commit it. I saw it before in my database
esat erkeç
March 1, 2011 at 10:46 pm
Hi All,
Thanks again for the feedback. We eventually did a restart and all is in order. We also got Microsoft to come have a look. They believe it was to do with a network issue. Something about the process not completely reading a packet.
Thanks Again.
Denesh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply