WHERE ja.session_id = (SELECT MAX(session_id) FROM ) ORDER BY job_name,job_status LEFT JOIN jh ON ja.job_history_id = jh.instance_id WHEN 7 THEN 'Performing completion actions'ĬONVERT(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,ĬONVERT(VARCHAR(500),jh.message) AS step_descriptionįROM OPENROWSET('sqloledb', 'server=(local) trusted_connection=yes', 'set fmtonly off exec _help_job') jsp Collect job activity from SQL 20 servers.ĬONVERT(varchar(500),j.name) AS job_name,ĬASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status, SELECT = CONVERT(TINYINT,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) The contents of the file should be as follows: This script will later be imported into the SSIS package. sql script file to select the job activity data from each server. Run the following script to create the table: Next, create a "JobDetails" table in a designated database. NOTE: SQL Server Express edition will not work as a host, as SSIS and SQL Agent will need to be running. Personally, I use a local instance SQL Server on my PC as the host. the server on which all SQL job activity will be gathered. STEP 1: Designate Host SQL Server / Create JobDetails Tableįirst, you must designate a SQL Server as a host, i.e. I tweaked Vidhya's script by removing the use of a temporary table, by adding the current_execution_status column to the SELECT statements, and by adding several formatting statements as to ensure the script would run within the SSIS construct. NOTE: The SQL script used in this solution was derived from Vidhya Sagar's blog at: The components of this solution are a SQL Server Integration Services (SSIS) package, a SQL script and a SQL Server Reporting Services (SSRS) report. This article illustrates a solution that will gather SQL job activity from all your SQL 2000, 20 servers and will email you a report according to a predetermined schedule.įigure 1 - SQL Job Activity Report Example Well, I recently grew very tired of this mundane routine and I decided it was time to automate the process using tools within the SQL Server arsenal. As DBAs, we often find ourselves spending a large portion of our day reviewing SQL Server job activity, and if you happen to have many SQL Servers with many jobs running on them and you don't happen to have an enterprise SQL job assessment tool like SQL Sentry, then you're probably accessing each server one at a time via Management Studio to view job statuses.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |