--
If the job got failed, success or cancel by anyone.
SELECT
count(*)
FROM
MSDB.DBO.SYSJOBSERVERS
SJS
LEFT
OUTER
JOIN
MSDB.DBO.SYSJOBS
SJ
ON
(SJ.JOB_ID
=
SJS.JOB_ID)
where
SJS.LAST_RUN_OUTCOME
in
(0,2)
--
0 failed 2 cannaclled
--
If the log file got full more than 80 percente
create
table
#TmpLOGSPACE(
DatabaseName
varchar(100)
,
LOGSIZE_MB
decimal(18,
9)
,
LOGSPACE_USED
decimal(18,
9)
,
LOGSTATUS
decimal(18,
9))
insert
#TmpLOGSPACE(DatabaseName,
LOGSIZE_MB,
LOGSPACE_USED,
LOGSTATUS)
exec
('DBCC
SQLPERF(LOGSPACE);')
select
count(*)
from
#TmpLOGSPACE
where
LOGSPACE_USED
>
40 --
This is value which can be change as per our requirement.
--
Drop table #TmpLOGSPACE
--
If the blocking is more than 10 minutes
SELECT
count
(*)
FROM
SYS.DM_EXEC_REQUESTS
REQ
CROSS
APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)
AS
SQLTEXT
WHERE
TOTAL_ELAPSED_TIME
>=600000
--
10 MINUTES
and
blocking_session_id
<>0
--
If query running more than 1 hour
SELECT
count(*)
FROM
SYS.DM_EXEC_REQUESTS
REQ
CROSS
APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)
AS
SQLTEXT
WHERE
TOTAL_ELAPSED_TIME
>=3600000
--
1 hour
end
--
If DB status is not online
SELECT
COUNT(*)
FROM
sys.databases
where
state_desc
<>
'Online'
