亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

查看job運行時間,以便不影響生產數據庫正常運行

系統 1994 0
You may find yourself in a position where you need to look at what jobs have been running over a specified
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.


JobID to ProcessID Function

This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.

     

CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)

RETURNS VARCHAR(8)

AS

BEGIN

RETURN (substring(left(@job_id,8),7,2) +

		substring(left(@job_id,8),5,2) +

		substring(left(@job_id,8),3,2) +

		substring(left(@job_id,8),1,2))

END

 


  

Return to Top


TSQL Code to Find Jobs Running Over x Minutes

The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.

     

DECLARE @MaxMinutes int

SET @MaxMinutes = 30

 

SELECT	p.spid, 

	j.name, 

	p.program_name, 

	isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning], 

	last_batch

FROM master..sysprocesses p

JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'

  AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes

 


  

You may find yourself in a position where you need to look at what jobs have been running over a specified
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.


JobID to ProcessID Function

This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.

     

CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)

RETURNS VARCHAR(8)

AS

BEGIN

RETURN (substring(left(@job_id,8),7,2) +

		substring(left(@job_id,8),5,2) +

		substring(left(@job_id,8),3,2) +

		substring(left(@job_id,8),1,2))

END

 


  

Return to Top


TSQL Code to Find Jobs Running Over x Minutes

The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.

     

DECLARE @MaxMinutes int

SET @MaxMinutes = 30

 

SELECT	p.spid, 

	j.name, 

	p.program_name, 

	isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning], 

	last_batch

FROM master..sysprocesses p

JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'

  AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes

 


  

查看job運行時間,以便不影響生產數據庫正常運行


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 九九影院 影片 | 天堂va亚洲va欧美va国产 | 日本永久视频 | 国产成在线人视频免费视频 | 成人性色生活片全黄 | 国产视频999| 人与禽交免费网站视频 | 国产精品乱码高清在线观看 | 在线视频中文 | 国产精品短篇二区 | 精品国产乱码久久久久久浪潮 | 亚洲欧美在线视频 | 国产免费人成在线视频视频 | 欧美激情在线 | 91国自产精品中文字幕亚洲 | 久久激情综合色丁香 | 亚洲免费在线播放 | 日本a视频在线 | 91精品国产麻豆国产自产在线 | 国产成人a视频在线观看 | 国产理论最新国产精品视频 | 福利网站在线观看 | 色婷婷免费视频 | 99精彩视频 | 色偷偷在线刺激免费视频 | 无遮挡一级毛片性视频不卡 | 久久成人国产精品二三区 | 中文字幕在线视频精品 | 亚洲国产一区二区三区在线观看 | 国产伦精品一区二区三区免费观看 | 日韩综合图区 | 国产成人精品高清在线观看99 | 久久青草免费97线频观 | 一级看片 | 人人揉揉香蕉大免费不卡 | 国产成a人片在线观看视频99 | 亚洲国产成人综合精品2020 | 欧美人牲囗毛片 | videoxxoo欧美老师| 久久久综合网 | 精品在线一区二区 |