Sunday, March 31, 2013

[MS SQL Server] Script to calculate when a job ended.

[MS SQL Server] Script to calculate when a job ended.

Script to calculate when a job ended.

Posted: 10 Mar 2013 12:31 AM PST

I need to alter the script listed below to calculate the DateTime that the Job completed and sort on the derived column in descending order.[code="sql"]select job_name, MAX(run_datetime) AS run_datetime, MAX(run_duration) AS run_durationfrom( select job_name, run_datetime, SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration from ( select DISTINCT j.name as job_name, run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4, run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) from msdb..sysjobhistory h inner join msdb..sysjobs j on h.job_id = j.job_id WHERE j.name NOT LIKE 'Backup%'AND j.name NOT LIKE 'Cleanup%'AND j.name NOT LIKE 'Shrink%'AND j.name <> 'WMI_Disk_Space_Notification' AND j.name <> 'syspolicy_purge_history' ) t) tWHERE run_dateTime >= '2013-03-09' AND run_dateTime < '2013-03-10'GROUP BY job_nameorder by job_name, run_datetime[/code]In addition to displaying the job_name, run_datetime and duration I want to deplay and sort by the Job_ENd_DateTime.Any help would be greatly appreciated.

Version Change in Change Tracking

Posted: 30 Mar 2013 11:49 PM PDT

Hi,If any INSERT, Update OR delete operation happens in the table than a new version (SYS_CHANGE_VERSION) will get created or not?I have found that for some bunch of insert operation same version(SYS_CHANGE_VERSION, SYS_CHANGE_CREATION_VERSION) got created.Thanks,Anupam
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog