Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

monitor Database job

ahhong

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

Find more posts tagged with

Accepted answers

All comments

alankar.srivastava

Hi,

You can monitor it through DPA module in Solarwinds.

Seashore

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!

ahhong

Haha. We did not have DPA. Thank

ahhong

May i know where do i input this custom query?