[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. |
| Posted: 09 Mar 2013 11:01 AM PST My database ''ABC" is configured with snapshot to Sub 1 and transactional replication to Sub 2. last week i drop transactional replication then i want to truncate all tables data keeping snapshot publication. i was not able truncate.Is this a bug ? after dropping snapshot replication i was able to truncate. Any clue? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment