none
Retrieve SQL Agent Process ID via T-SQL below SQL Server 2008 R2 SP1.

    Question

  • Dear Folks,

    I have to retrieve the process ID of SQL Agent (as shown in Configuration Manager) via T-SQL Script. It can be easily retrieved in SQL Server 2008 R2 SP1 or above using 'sys.dm_server_services' DMV. But I have to retrieve it in SQL Server 2008 R2 RTM and above DMV was not introduced in it. Using XP_cmdshell 'tasklist' is useless as well, as it doesn't reflect the instance specific details of SQLAgent process. Please Suggest the solution. Thanks in advance. :)   


    Pranshul Gupta



    • Edited by Pranshul Gupta Friday, December 27, 2013 12:51 PM sys.dm_exec_requests is replaced by 'sys.dm_server_services'
    Friday, December 27, 2013 11:50 AM

Answers

All replies

  • Dear Folks,

    I have to retrieve the process ID of SQL Agent (as shown in Configuration Manager) via T-SQL Script. It can be easily retrieved in SQL Server 2008 R2 SP1 or above using 'sys.dm_exec_request' DMV. But I have to retrieve it in SQL Server 2008 R2 RTM and above DMV was not introduced in it. Using XP_cmdshell 'tasklist' is useless as well, as it doesn't reflect the instance specific details of SQLAgent process. Please Suggest the solution. Thanks in advance. :)   


    Pranshul Gupta


    Hello,

    Sys.dm_exec_requests DMV was introduced from sql server 2005.See this link

    http://technet.microsoft.com/en-us/library/ms177648(v=sql.90).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621 Friday, December 27, 2013 12:24 PM updated link previous was wrong
    Friday, December 27, 2013 12:19 PM
  • Mistakenly I reffered another DMV, I mean to say that 'sys.dm_server_services' was not introduced in SQL Server 2008 R2 RTM. I have replaced the reference in the original question. Thanks for your attention Shanky.

    PGupta

    Friday, December 27, 2013 12:55 PM
  • SELECT [host_process_id]
    FROM sys.dm_exec_sessions
    WHERE[program_name] LIKE 'SQLAgent - %'
    GROUP BY [host_process_id]
    Will this not work?


    Jon

    Friday, December 27, 2013 1:10 PM
  • Its not working Jon , thanks for attention. :)

    PGupta

    Friday, December 27, 2013 1:37 PM
  • Its not working Jon , thanks for attention. :)

    PGupta

    Not working ,What do you mean by that.Script is correct run DMV in SQL server 2008 R2 and note Process_id and then run the script it will give same process id .Hope this is what you were looking for

    What are you trying to get


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, December 27, 2013 2:26 PM