Monday, 1 January 2018

5 important queries to monitor SQL server related issues



-- 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'