I’ve 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. I’ve tried sp_help_jobactivity for SQL2K5 \ SQL2k8, however it doesn’t provide the columns needed. So I’ve 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
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008 and
- SQL Server 2008 R2
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 won’t 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.
sqlcmd –E –S Servername –i”file path” > “Output Path”
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
sqlcmd –E –S SAGARPC –i”C:\temp\job_details.sql” > “C:\temp\job_activity_details.txt”
sqlcmd –E –S SAGARPC\SQL2008 –i”C:\temp\job_details.sql” >> “C:\temp\job_activity_details.txt”
I’ve saved the SQL script file to C:\temp\job_details.sql path and I’ve 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:\temp\job_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, please write it in our discussion board.