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 |
|
Henrik Svensson
Starting Member
25 Posts |
 Posted - 2010-03-18 : 10:58:01
|
| Hi!I know I can use KILL WITH STATUSONLY and get information on the following form:Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.My question is:Is there a way to get a hold of this information from a system view or is an estimation of the time left only available by using the KILL statement? I am thinking about presenting this information in a GUI and instead of parsing this string (which I guess is also translated depending on localization?) it would be nicer to get the information from columns in a system view.With best regards,Henrik Svensson |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 11:06:32
|
Wow, you must be killing a lot of processes if you actually want to build a GUI for it!I'd address the reasons I'm killing that many SPIDs instead  |
 |
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2010-03-19 : 08:35:32
|
| Well, I might start by addressing the reasons you assume I am killing a lot of processes, which I am not (nor the system I have been assigned to work with) :-). A session does not have to be killed to initiate a rollback. I could give you a very long explanation of exactly what the system is doing and why it is in the certain state it is, but that's irrelevant. I just want to know if there is an alternative to the KILL WITH STATUSONLY statement. If you know the answer of my question I would be grateful for that answer.With best regards,Henrik Svensson |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-19 : 12:51:37
|
| might be able to use sys.dm_exec_requests |
|
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2010-03-28 : 07:34:33
|
| Thanks, Russell! I could use the percent_complete column for a session with status = 'rollback' in sys.dm_exec_requests. And now I only had to add the VIEW SERVER STATE permission to the user monitoring the rollback progress. KILL on the other hand requires the user to belong to the sysprocesses or sysadmin roles, which is less secure. Thank you!With best regards,Henrik Svensson |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-29 : 12:30:31
|
| You're Welcome! |
|
|
|
|
|
|
Rollback information without KILL