Ive written a script to find the job activity details, this script will give the output similar to job activity monitor in SQL2K5 or SQL2K8. In my working place we had a request to collect the job details from all the servers which includes all version of SQL Server. Ive tried sp_help_jobactivity for SQL2K5 SQL2k8, however it doesn’t provide the columns needed. So Ive written a script to get customized output, this script will also work with SQL2K. The script will fetch the following details
- Server Name
- Job Name
- Job Status
- Last Run Status
- Last Run Date and Time
- Run Duration
- Next Run Date and Time
- Message about status of the job
Works with
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008 and
- SQL Server 2008 R2
Script
SET NOCOUNT ON --Checking for SQL Server verion IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8 BEGIN ---This is for SQL 2k5 and SQL2k8 servers SET NOCOUNT ON SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName, j.name AS job_name, CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status, CASE jh.run_status WHEN 0 THEN 'Error Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' ELSE 'Status Unknown' END AS 'last_run_status', ja.run_requested_date as last_run_date, CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration, ja.next_scheduled_run_date, CONVERT(VARCHAR(500),jh.message) AS step_description FROM (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id) join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status END ELSE BEGIN --This is for SQL2k servers SET NOCOUNT ON DECLARE @SQL VARCHAR(5000) --Getting information from sp_help_job to a temp table SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status, CASE last_run_outcome WHEN 0 THEN ''Error Failed'' WHEN 1 THEN ''Succeeded'' WHEN 2 THEN ''Retry'' WHEN 3 THEN ''Cancelled'' WHEN 4 THEN ''In Progress'' ELSE ''Status Unknown'' END AS last_run_status, CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date, CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time, CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date, CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time, last_run_date AS lrd, last_run_time AS lrt INTO ##jobdetails FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')' exec (@SQL) --Merging run date & time format, adding run duration and adding step description select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status, CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date, CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration, CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date, CONVERT(VARCHAR(500),jh.message) AS step_description from (##jobdetails jd LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null order by jd.job_name,jd.job_status --dropping the temp table drop table ###jobdetails END
How to use?
Very simple, just copy the script and paste it in client tools and run it, it will collect the information for you. We are just doing select on tables across msdb database, hence it wont affect your system. Sample output is shown below
How to use the script in batch file?
You can also use this script in a batch file to get these details from all the servers in your environment. Save the script to a file in a path. Sample batch file command is shown below.
Syntax
sqlcmd E S Servername ifile path > Output Path
Where,
E Windows authentication, if you are connecting with SQL authentication then use U & P parameter
S Provide the server name
i Input SQL file to execute
> Create new output file
>> Append to the existing output file
Sample Command
sqlcmd -E -S SAGARPC -iC:\tempjob_details.sql > C:\tempjob_activity_details.txt
sqlcmd -E -S SAGARPC\SQL2008 -iC:\tempjob_details.sql >> C:\tempjob_activity_details.txt
Ive saved the SQL script file to C:tempjob_details.sql path and Ive used this as input file. You can add N number of servers to the batch file and save the file as *.bat and just execute it, the batch file will collect the information to the file C:tempjob_activity_details.txt. If needed you can add a mail step to send the output to the respective recipients.
If you have any questions or suggestions related to this script write it in comments
Leave a Reply to VidhyaSagar Cancel reply