Sunday, March 10, 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.

Replication???

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?

No comments:

Post a Comment

Search This Blog