|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sysprocesses vs jobHi,
Is there any way to identify job in master..sysprocesses table? Thank you. Also, it happens all the time -- I cancelled the job, but the job processes id keep stay active all the time. Issue KILL statement, but the processes still stay runnable all the time until restart the SQL server? Any idea for this issue. -- Best Regards, Lynn Lynn (L***@discussions.microsoft.com) writes:
> Is there any way to identify job in master..sysprocesses table? Thank What state is it in? If process was in a transaction, it can take a > you. > > Also, it happens all the time -- I cancelled the job, but the job > processes id keep stay active all the time. Issue KILL statement, but > the processes still stay runnable all the time until restart the SQL > server? Any idea for this issue. long time to rollback. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx At least with sql2k sp4, if you select * from sysprocesses, the column
program_name will contain an entry like SQLAgent - TSQL JobStep (Job 0x3459258D70D52A41B2D24B37B1FD2822 : Step 1). The GUID in this line can then be cross referenced to msdb..sysjobs job_id. Also, if you know the TSQL the job is running, the sql_handle in sysprocesses can tell you what sql batch or statement is currently running as well. Show quote "Lynn" wrote: > Hi, > > Is there any way to identify job in master..sysprocesses table? Thank you. > > Also, it happens all the time -- I cancelled the job, but the job processes > id keep stay active all the time. Issue KILL statement, but the processes > still stay runnable all the time until restart the SQL server? Any idea for > this issue. > -- > Best Regards, > > Lynn John,
Thank you. Yes. it's did cross reference with the job_id for the last two segments without '-'. (I compareed the begin of string before and it didn't work :) You mentioned sql_handle, they all looks like the codeing for me, such as 0x0100120044C7142BC0A0F8490000000000000000. How can I read those binary file? Thank you again. Also, it happens all the time -- I cancelled the job, but the job processes id keep stay active all the time. Issue KILL statement, but the processes still stay runnable all the time until restart the SQL server? Any idea for this issue. thank you again -- Show quoteBest Regards, Lynn "John T" wrote: > At least with sql2k sp4, if you select * from sysprocesses, the column > program_name will contain an entry like SQLAgent - TSQL JobStep (Job > 0x3459258D70D52A41B2D24B37B1FD2822 : Step 1). The GUID in this line can then > be cross referenced to msdb..sysjobs job_id. Also, if you know the TSQL the > job is running, the sql_handle in sysprocesses can tell you what sql batch or > statement is currently running as well. > > "Lynn" wrote: > > > Hi, > > > > Is there any way to identify job in master..sysprocesses table? Thank you. > > > > Also, it happens all the time -- I cancelled the job, but the job processes > > id keep stay active all the time. Issue KILL statement, but the processes > > still stay runnable all the time until restart the SQL server? Any idea for > > this issue. > > -- > > Best Regards, > > > > Lynn Lynn (L***@discussions.microsoft.com) writes:
> You mentioned sql_handle, they all looks like the codeing for me, such as You can use fn_get_sql to get the actual statement. Since I'm too lazy > 0x0100120044C7142BC0A0F8490000000000000000. How can I read those binary > file? Thank you again. to look up the code, I just point you do my procedure aba_lockinfo that will do that work for you. It's at http://www.sommarskog.se/sqlutil/aba_lockinfo.html. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics