cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

monitor Database job

Dear expert,

I am new to solarwinds and will like to monitor database job status, alive and size of directory.

Anyone know is there any custom queries to the above.

Or any solution.. 

0 Kudos
4 Replies
Level 13

As you might know, in SAM you can use SQL queries against the SQL databases to collect information. The only thing you have to consider is that the output of the script have to be on single numeric value (and an optional string). The query also have to give you a 0 if there is no result, null is not allowed.

So when creating a query, think like this for example: "how many SQL agent jobs has failed last x hours"

Here is an example of that:

-- Variable Declarations
DECLARE @Count INT
Declare @TimeCheck AS Datetime
Declare @SearchFor AS char (4)

-- Initialize Variables
SET @Count = 0
set @TimeCheck = CONVERT(datetime,DATEADD(HOUR,-20,SYSDATETIMEOFFSET())) -- How long back to search for failed jobs
Set @SearchFor='MT - ' --Job Name string to search for, job name starting with this, edit char lenght for number of characters

Begin

Select @Count = COUNT(*) from (
	select run_status
	 from dbo.sysjobhistory h
		INNER JOIN msdb.dbo.sysjobs j
	   ON h.job_id = j.job_id
	where
        CAST 
        ( 
            CONVERT(VARCHAR, h.run_date) 
            + ' ' + STUFF(STUFF(RIGHT('000000' 
            + CONVERT(VARCHAR,h.run_time),6),5,0,':'),3,0,':') 
            AS DATETIME 
        )> @TimeCheck	AND	
		h.run_status=0 AND
		h.step_id<>0 AND
		j.[name] LIKE @SearchFor + '%'
	) as t

If (@Count =0)
	select 0
else
	select 
		count(*) as "jobs",
		j.[name]		
	 from dbo.sysjobhistory h
		INNER JOIN msdb.dbo.sysjobs j
	   ON h.job_id = j.job_id
	where
        CAST 
        ( 
            CONVERT(VARCHAR, h.run_date) 
            + ' ' + STUFF(STUFF(RIGHT('000000' 
            + CONVERT(VARCHAR,h.run_time),6),5,0,':'),3,0,':') 
            AS DATETIME 
        )> @TimeCheck	AND	
		h.run_status=0 AND
		h.step_id<>0 AND
		j.[name] LIKE @SearchFor + '%'
	group by j.[name];
End

 

Another check could be "how many databases has not been backed up last xx hours":

SELECT 
	d.database_id,
	DatabaseName = d.name ,
	LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date)),'No backup'),
	'Days' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24,
	'Hours' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
INTO #BackupInfo
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN ('tempdb','model','ReportServerTempDB','ReportServer','master','msdb') and d.state != 6
GROUP BY d.database_id, d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2

IF (Select count(*) as "FirstCount" from(select b.database_id from #BackupInfo b) as FC)>0
BEGIN
	Select 
		count(*) as "Rowcount", 
		t.Backupinfo 
		from(
			Select 
				substring(
				(
					Select ', '+bkt1.DatabaseName  AS [text()]
					From #BackupInfo bkt1
					For XML PATH ('')
				)
				, 2, 1000) [Backupinfo]
	From #BackupInfo bkt2) 
	as t
	group by t.backupinfo
END
ELSE
Select 0

drop table #backupinfo

 

As you see there is some extra code in the end, that is so that you make sure to receive a 0 when there is no failed backups AND to get the name of the databases with failing backups on one row.

So everything is possible, just ask SQL the right question 🙂

 

Good luck!

0 Kudos

May i know where do i input this custom query? 

0 Kudos

Hi,

You can monitor it through DPA module in Solarwinds.

0 Kudos

Haha. We did not have DPA. Thank

0 Kudos