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

查看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條評論
主站蜘蛛池模板: 中文字幕在线视频不卡 | 青青草好吊色 | 美女胸又大又黄www网站 | 国产精品午夜免费福利视频 | 色综合久久久久久久久五月 | 久久综合精品不卡一区二区 | 欧美一区二区在线免费观看 | www.国产视频 | 综合亚洲一区二区三区 | 一级毛片播放 | 欧美精品亚洲 | 日本黄 色 成 年 人免费观看 | 日本永久视频 | 国产亚洲精品福利 | 日韩精品久久不卡中文字幕 | 亚洲国产欧美国产综合一区 | 欧美一级毛片一 | 不卡一级毛片免费高清 | 欧美一欧美一级毛片 | 久青草视频免费视频播放线路1 | 久久久久国产精品四虎 | 福利午夜影院 | 日韩欧美视频在线播放 | 国产成人a一区二区 | 欧美日韩亚洲国产无线码 | 国产福利91精品一区二区三区 | 日本黄色录像视频 | 欧美成人特黄级毛片 | 成人网影 | 久久99热精品这里久久精品 | 日本久久久久中文字幕 | 精品国产96亚洲一区二区三区 | 国产这里有精品 | 国产精品高清在线 | 一级大毛片 | 久久精品女人天堂 | 思99热精品久久只有精品 | 日韩高清在线播放不卡 | 天天爽影院一区二区在线影院 | 亚洲精品在线网 | 亚洲天天做夜夜做天天欢 |