SQLServerJobsModule allows you to generate a report about SQL Server Jobs from several servers. Allows you to specify a time window for the report. Allows you get details about connections in packages IS.
Minimum Requirements
Server
- SQL Server 2008
- PowerShell 4.0 with remoting enabled needed on the host for Windows commands
Workstation
- Windows 7 with PowerShell v4
- SQL Server 2008 R2 SMO or SSMS
SQLServerJobsModule is not required on the server.
I recommend to use the script at client stations, but I encourage to make a test at servers.
Install
1. Install from the PowerShell Gallery
Install-Module -Name SQLServerJobsModule
The PowerShell Gallery and the command Install-Module are natively available in Windows 10. If you run Windows 7 or 8 or 8.1, you can either install PackageManagement from powershellgallery.com
2. Download the zip directly and manually import the module.
|
1 2 3 |
Invoke-WebRequest <a href="https://github.com/MateuszNad/SQLServerJobsModule/archive/master.zip">https://github.com/MateuszNad/SQLServerJobsModule/archive/master.zip</a> -Outfile ssum.zip Expand-Archive ssum.zip -DestinationPath . Import-Module .\SQLServerUpdatesModule-master\SQLServerUpdatesModule.psd1 |
Using module
Get-SQLServerJobs – allows get all Jobs on SQL Server.
Get-SQLServerJobs -InstanceServer IT-MN-M\MSSQLSERVER14
„IT-MN-M\MSSQLSERVER14” | Get-SQLServerJobs | Select JobName,NextRun
Show-SQLServerUpdatesReport – returns information about SQL Server Jobs in the format html.
Show-SQLServerJobsReport -InstanceServer IT-MN-M\MSSQLSERVER14 -Path C:\temp
Report about all SQL Server Jobs. Report saved in path C:\temp.
Show-SQLServerJobsReport -InstanceServer IT-MN-M\MSSQLSERVER14 -Path C:\temp\ -StartTime -EndTime
Allows get all Jobs on server which it will starts at a specific time and it will ends at a specific time. Report saved in path C:\temp.
Show-SQLServerUpdatesReport -ServerInstance it-mn-m\mssqlserver14, test-agsqlserver -HTML -Path C:\temp\report.html
Additional information.
Used T-SQL query :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
--Query - Getting all information about jobs on SQL Server WITH jobDuration AS ( SELECT max(jb.instance_id) as instance_id, job_id, max(jb.run_duratiON) as run_duratiON FROM ( SELECT max(cast(job_id as varchar(max))) as job_id, instance_id, avg(sjh.run_duratiON) as run_duratiON FROM msdb.dbo.sysjobhistory sjh GROUP BY sjh.instance_id) as jb GROUP BY jb.job_id ) SELECT j.name AS 'JobName', jst.step_name AS 'JobStep', j.descriptiON AS 'DescriptiON', jst.command AS 'Command', jst.subsystem AS 'Type', sch.name 'SchedulerName', ((jd.run_duratiON / 10000 * 3600) + ((jd.run_duratiON % 10000) / 100 * 60) + (jd.run_duratiON % 10000) % 100)/60 AS AvgDuratiONMin, CASE WHEN sch.enabled = 0 THEN 'Disable' WHEN jsch.next_run_date = 0 THEN SUBSTRING(CONVERT(CHAR(8),sch.active_start_date),7,2) + '-'+ SUBSTRING(CONVERT(CHAR(8),sch.active_start_date),5,2) + '-' + SUBSTRING(CONVERT(CHAR(8),sch.active_start_date),1,4)+ ' ' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),sch.active_start_time),6),1,2) + ':'+ SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),sch.active_start_time),6),3,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),sch.active_start_time),6),5,2) ELSE SUBSTRING(CONVERT(CHAR(8),jsch.next_run_date),7,2) + '-'+ SUBSTRING(CONVERT(CHAR(8),jsch.next_run_date),5,2) + '-' + SUBSTRING(CONVERT(CHAR(8),jsch.next_run_date),1,4)+ ' ' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),jsch.next_run_time),6),1,2) + ':'+ SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),jsch.next_run_time),6),3,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),jsch.next_run_time),6),5,2) END AS NextRun, sch.freq_type, sch.freq_interval, sch.freq_subday_type, sch.freq_subday_interval, sch.freq_relative_interval, sch.freq_recurrence_factor, @@SERVERNAME AS Server FROM msdb.dbo.sysjobschedules jsch JOIN msdb.dbo.sysjobsteps jst ON jsch.job_id = jst.job_id JOIN msdb.dbo.sysjobs j ON jsch.job_id = j.job_id JOIN msdb.dbo.sysschedules sch ON jsch.schedule_id = sch.schedule_id JOIN jobduratiON jd ON j.job_id = jd.job_id |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--Query - Getting information about SSIS while exists on SQL Server WITH SSIS AS ( SELECT DENSE_RANK() OVER(PARTITION BY project_id ORDER BY project_id, project_version_lsn DESC) AS rnk, * FROM [SSISDB].internal.object_parameters ) SELECT p.Name AS NameSSIS, --deployed_by_name, --last_deployed_time, object_version_lsn AS VersionSSIS, --object_name AS ParameterName, parameter_name AS ParameterName, design_default_value AS ParameterValue FROM [SSISDB].[internal].[projects] P JOIN SSIS ON P.project_id = SSIS.project_id WHERE SSIS.rnk = 1 and SSIS.parameter_name like '%ServerName' |
Errors and problems
Please send me information about errors and problems by email.
Mateusz Nadobnik
Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.
read more
Darmowy ebook “Zacznij z PowerShell”
Pobierz “Zacznij z PowerShell” – aby zacząć przygodę z poleceniami, funkcjami, skryptami które pozwolą uprościć, przyśpieszyć i zaautomatyzować Twoje codzienne zadanie, nie tylko z obszaru IT.