cancel
Showing results for 
Search instead for 
Did you mean: 

DBA Reports

nbritton
Level 4

Does anyone else have SQL DBA's asking for reports that show what databases have failed durring a backup.   I am being asked to provide a report that will show the db that failed, not only the server, or instances but the database.

How is anyone else handling this?

4 REPLIES 4

nbritton
Level 4

I did end up finding a table that may assist.  I am currently trying to do some testing.  That table is : nb_JobDbInstanceArchive

 

I am expecting this attempt to look at all children jobs.

 

My current script is :

 

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.policytype as 'Policy Type',
nb_JobDbInstanceArchive.dbname,

LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' 
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
            (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              
                              BKP.clientId = A.clientId AND
                              BKP.policyId = A.policyId AND
                              BKP.policyName = A.policyName AND
                              BKP.policyType = A.policyType AND
                             ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
FROM  "domain_JobArchive" A , "domain_masterserver" B , nb_JobDbInstanceArchive
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0 AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2) AND
A.policytype = '15' AND
A.id = nb_JobDbInstanceArchive.jobid AND A.masterserverid = nb_JobDbInstanceArchive.masterserverid AND A.clientname = nb_JobDbInstanceArchive.clientname
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,A.clientid,policyid,dbname
ORDER BY B.friendlyName, 'Days since Last successful';

output to c:\data\output\result-test.txt;
go
 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Did it work?

no i have not gotten a query to work well yet.

Apparently this is a known issuie and is currenlty being adresses as an eeb.    With SIPs the data is supposed to be in that table, but is not.  The eeb should correct that.