![]() Note that if you just leave as null, it will return for all jobs. LEFT OUTER JOIN sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)), Inner join msdb.sysjobs sj on xpr.job_id = sj.job_id Now I am seeing access is denied and none of the functionalities are working under activity monitor. I have granted Performance Monitor Users under lusrmgr. When 7 then 'Performing completion actions' DBAs - I am seeing Processes section under SSMS - Activity monitor is greyed out for one of the windows login. INSERT INTO _sqlagent_enum_jobs 1, sj.name,Ĭase xpr.job_state when 1 then 'Executing: ' + cast(sjs.step_id as nvarchar(2)) + ' (' + sjs.step_name + ')' Request_source_id sysname COLLATE database_default NULL, INSERT INTO _sqlagent_enum_jobs INTO _sqlagent_enum_jobs can take this further, copy the structure of the temporary table and call it yourself so that you can then cherry pick the results: declare sysname = 'fill in your jobname here' ĭeclare uniqueidentifier = (select top 1 job_id from msdb.sysjobs where name = sysname = (SELECT SUSER_SNAME()) If you do a little digging inside sp_help_job, you can see that xp_sqlagent_enum_jobs is called with the following parameters: IF / 0x01000000) >= 8) - SQL Server 8.0 or greater If you call sp_help_job with no paramters, it will include the columns current_execution_status and current_execution_step which is what you are after. ![]() It is accessed through sp_help_job which is documented here. This blog describes this and the required permissions for viewing all of Activity Monitor. The table which holds the status information is actually an extended stored procedure called xp_sqlagent_enum_jobs. Activity Monitor requires the firewall to allow access to the SQL Server port (default 1433) for SQL query access and the RPC ports for WMI access for things like the Processor, Network, Disk IO and Memory graphs. "Run Query" is the name of step 1 of the job. How to fix it: Use regedit to find HKEYLOCALMACHINE\SYSTEM\CurrentControlSet\Services\PerfProc\Performance there is a key in there called Disable Performance Counters, delete it or set it to 0 You may need a restart after you change the key. the status the job ended at? In the example above I would want to return "Executing 1(Run Query)". I'm using the queries found on this site (query also below), which are great but the status it returns is the status of a job that has already been run.ĭoes anyone know of a way to query the current status vs. ![]() This field exists under the "Status" column in Job Activity Monitor. I'm trying to write a query to find the current status of a SQL Agent Job. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |