Help - is my spid rolling back or not? 1

[LFCfan]

(SQL Server 2005)

I have a process that was killed.

sp_who2 'active' now shows this spid with a status of suspended, and a command of KILLED/ROLLBACK. The DiskIO figure is changing on each refresh of sp_who2. The LastBatch timestamp isn't changing - it's displaying a time of 3 hours ago.

The status in sys.dm_exec_requests is "supended" and not "rollback", and the waittype is PAGEIOLATCH_EX.

The sys.sysprocesses entry for the spid indicates that the waitresource (the page number) does change on each refresh of the result set.

I am very confused as to whether my process is actually rolling back or is hung indefinitely waiting for something.
Is there anything else I can look at to help clarify the issue?

Unfortunately we have no proper DBA, and I'm having to be a DBA without having the necessary experience/training!

Many thanks for reading, and of course if you need any more info from me I will provide it.


~LFCfan

 

[LFCfan]

I didn't manage to come to any conclusion, ended up restarting the SQL Server service. Now waiting for the database to restore itself...

~LFCfan

 

[mrdenny]

If the IO is changing then the spid is rolling back.

This is now confirmed by the fact that the database is restoring after you restarted the service.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[LFCfan]

Thanks Denny, I'll bear that in mind for future panic-stricken days

~LFCfan