August 21, 2013 at 12:26 am
Hello
I'm trying to map the SPID of a certain process to the PID under the processes tab in task manager. I can map the PID to the KPID through the sysprocesses table but this KPID does not appear in task manager under the PID's under the processes tab.
How can this be done?
August 21, 2013 at 12:36 am
You can't. Are trying to track down which OS process was spawned by a call to xp_cmdshell by any chance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2013 at 12:41 am
No, We have 3rd party backup software which has made a backup call to SQL server, this process has been stuck in a rollback state for over a week and is blocking all other backup attempts. This is happened before and the only way we could resolve the issue was to restart the SQL server service. So i'm looking at other ways for a resolution
August 21, 2013 at 1:19 am
Restart SQL is the solution. DO NOT attempt to kill the windows thread, it cause stack dumps, kill the SQL process, break all sorts of things
http://sqlblog.com/blogs/linchi_shea/archive/2010/02/04/killing-a-sql-server-thread-don-t.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2013 at 3:00 am
Matthew.Peters 13064 (8/21/2013)
No, We have 3rd party backup software which has made a backup call to SQL server, this process has been stuck in a rollback state for over a week and is blocking all other backup attempts. This is happened before and the only way we could resolve the issue was to restart the SQL server service. So i'm looking at other ways for a resolution
A call to the native BACKUP DATABASE command or maybe a call to an extended stored procedure that may have spawned a separate OS process that runs a third-party executable?
Killing an OS process that was spawned by a SQL session, e.g. a cmd.exe process spawned by xp_cmdshell, is a safe thing to do however in this scenario there is no way to tie the SPID to the PID (my 'you can't' response above). What Gail referred to, peering into the sqlservr.exe process and killing a specific thread that belongs to that process is extremely ill-advised for the reasons mentioned in the article.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2013 at 10:58 pm
OK Thanks for the help guys. I was exploring other options than restarting sql server as it's a production box, but it looks like that's the only option available.
After further investigation we found the issue was caused from a stack dump which spawned from a query that was run from our 3rd party backup software
March 16, 2015 at 8:45 am
Sorry to come to this thread late but it might help someone else in the future.
You can tie a spid back to a host process and host name with this little query (2005 onwards) :-
select hostname, hostprocess
from master..sysprocesses
where spid = xxx
Where xxx is the spid that is doing the blocking.
What you want to do with that process given the caveats above is completely up to you..
March 20, 2015 at 4:26 am
bobjbain 6094 (3/16/2015)
Sorry to come to this thread late but it might help someone else in the future.You can tie a spid back to a host process and host name with this little query (2005 onwards) :-
select hostname, hostprocessfrom master..sysprocesses
where spid = xxx
Yep, Linchi was using sysprocesses in the article linked to by Gail above.
Where xxx is the spid that is doing the blocking.
What you want to do with that process given the caveats above is completely up to you..
Note there is a fundamental difference between hostprocess and kpid:
select hostname, hostprocess, kpid
from master..sysprocesses
where spid = xxx
Killing a Windows OS process where the PID shown in Task Manager matches the hostprocess value returned by the query above is safe to do. It would be analog to a data-client restarting their computer while they had an open session with SQL Server.
Killing a Windows OS thread that is a child thread of the SQL Server host-process (sqlservr.exe) where the OS thread ID shown in Process Explorer matches the kpid returned by the query above is what is ill-advised and is the subject of Linchi's article.
PS Note that sysprocesses has been deprecated since SQL 2005.
Just to have a little more fun I worked this query up using the recommended System Views. It's a bit wordy compared to using sysprocesses and I had to go digging to get the thread info as Microsoft does not mention all the necessary views when they list the replacements for sysprocesses:
select s.host_name, s.host_process_id, t.os_thread_id, s.session_id
from sys.dm_exec_sessions s
join sys.dm_exec_requests er on s.session_id = er.session_id
join sys.dm_os_workers w on er.task_address = w.task_address
join sys.dm_os_threads t on w.thread_address = t.thread_address
where s.session_id = xxx;
Mapping System Tables to System Views (Transact-SQL)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 17, 2015 at 8:29 am
thanks for this. IT really helped alot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply