When I was running some cmd through xp_cmdshell in sql server it tooks long time to execute.So I have killed the session.But it stucks in KILLED/ROLLBACK state for long time.Its a standalone production server.I cant restart this server.Is this a performance issue? Is there any way to clear that session?
Asked
Active
Viewed 5,653 times
2
-
how much it took time ? how much record we are talking ? are there indexex ? as far as i know you have to wait to rollback unless you migh loose data – Moudiz Jun 08 '17 at 07:16
-
what sort of script were you running? – RoMEoMusTDiE Jun 08 '17 at 07:25
-
exec xp_cmdshell 'typeperf "\LogicalDisk(*)\Avg. Disk sec/Read"' @maSTAShuFu – SujithTee Jun 08 '17 at 07:39
-
Actually which was cmdshell command and did not return any output @Moudiz – SujithTee Jun 08 '17 at 07:44
-
try KILL spid WITH STATUSONLY; to get a progress report and an estimated time to rollback – Steve Ford Jun 08 '17 at 13:24
-
progress 0% @SteveFord – SujithTee Jun 09 '17 at 05:47
-
@SujithTee I think that xp_cmdshell spawns a windows process to run. You need to find this windows process and kill it. Take a look at this link https://www.sqlservercentral.com/Forums/Topic1733632-391-1.aspx – Steve Ford Jun 09 '17 at 15:34
1 Answers
2
Kill the windows process linked to xp_cmdshell and if it does not rollback I guess the only solution will be to restart the instance.
Hybris95
- 2,286
- 2
- 16
- 33
-
Any way to do this programatically within SQL? IE have an agent job that automatically kills a windows process if its been stuck in that state for more than x minutes? – Geoff Griswald Jan 21 '20 at 09:33
-
There may be, but actually asking your system administrator would be the best bet – Hybris95 Jan 22 '20 at 09:24
-
How to protect against a process run from xp_cmdshell remaining in KILLED/ROLLBACK state forever? - https://stackoverflow.com/questions/59838503/how-to-protect-against-a-process-run-from-xp-cmdshell-remaining-in-killed-rollba – Geoff Griswald Feb 03 '20 at 17:03