This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • Hi,

    You can monitor it through DPA module in Solarwinds.

  • 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!

  • Haha. We did not have DPA. Thank

  • May i know where do i input this custom query?