Hello,
so here is something weird i come across... maybe its a simple setting fixed... but what i have below runs on a SQL execute SQL task, and runs fine... HOWEVER for some reason, doesnt insert the Always on AG information... here is the tsql below:
truncate table dbo.Backup_Metrics
;WITH backupsetSummary
AS ( SELECT bs.database_name ,
bs.type bstype ,
MAX(backup_finish_date) MAXbackup_finish_date
FROM msdb.dbo.backupset bs (nolock)
GROUP BY bs.database_name ,
bs.type
),
MainBigSet
AS ( SELECT
@@SERVERNAME servername,
db.name ,
db.state_desc ,
db.Compatibility_level,
db.recovery_model_desc ,
bs.type ,
convert(decimal(10,2),bs.backup_size/1024.00/1024) backup_sizeinMB,
bs.backup_start_date,
bs.backup_finish_date,
physical_device_name,
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMins
FROM master.sys.databases db (nolock)
LEFT OUTER JOIN backupsetSummary bss (nolock) ON bss.database_name = db.name
LEFT OUTER JOIN msdb.dbo.backupset bs (nolock) ON bs.database_name = db.name
AND bss.bstype = bs.type
AND bss.MAXbackup_finish_date = bs.backup_finish_date
JOIN msdb.dbo.backupmediafamily m (nolock) ON bs.media_set_id = m.media_set_id
where db.database_id>4
)
,AlwaysOnInfo
as
(
select primary_replica,primary_recovery_health_desc,synchronization_health_desc,automated_backup_preference_desc,database_name,groups.name AGName
from sys.dm_hadr_availability_group_states States (nolock)
INNER JOIN master.sys.availability_groups Groups (nolock) ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGDatabases (nolock) ON Groups.group_id = AGDatabases.group_id
)
-- select * from MainBigSet
insert into dbo.Backup_Metrics
SELECT
servername,
name,
Compatibility_level,
state_desc,
recovery_model_desc,
[master].sys.fn_hadr_backup_is_preferred_replica(name) as BackupPreferred,
Last_Backup = MAX(a.backup_finish_date),
Last_Full_Backup_start_Date = MAX(CASE WHEN A.type='D'
THEN a.backup_start_date ELSE NULL END),
Last_Full_Backup_end_date = MAX(CASE WHEN A.type='D'
THEN a.backup_finish_date ELSE NULL END),
Last_Full_BackupSize_MB= MAX(CASE WHEN A.type='D' THEN backup_sizeinMB ELSE NULL END),
DurationSeocnds_Full = MAX(CASE WHEN A.type='D'
THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END),
Last_Full_Backup_path = MAX(CASE WHEN A.type='D'
THEN a.physical_Device_name ELSE NULL END),
Last_Diff_Backup_start_Date = MAX(CASE WHEN A.type='I'
THEN a.backup_start_date ELSE NULL END),
Last_Diff_Backup_end_date = MAX(CASE WHEN A.type='I'
THEN a.backup_finish_date ELSE NULL END),
Last_Diff_BackupSize_MB= MAX(CASE WHEN A.type='I' THEN backup_sizeinMB ELSE NULL END),
DurationSeocnds_Logs = MAX(CASE WHEN A.type='I'
THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END),
Last_Diff_Backup_path = MAX(CASE WHEN A.type='I'
THEN a.physical_Device_name ELSE NULL END),
Last_Log_Backup_start_Date = MAX(CASE WHEN A.type='L'
THEN a.backup_start_date ELSE NULL END),
Last_Log_Backup_end_date = MAX(CASE WHEN A.type='L'
THEN a.backup_finish_date ELSE NULL END),
Last_Log_BackupSize_MB= MAX(CASE WHEN A.type='L' THEN backup_sizeinMB ELSE NULL END),
DurationSeocnds = MAX(CASE WHEN A.type='L'
THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END),
Last_Log_Backup_path = MAX(CASE WHEN A.type='L'
THEN a.physical_Device_name ELSE NULL END),
[Days_Since_Last_Full_Backup] = DATEDIFF(d,(MAX(CASE WHEN A.type='D'
THEN a.backup_finish_date ELSE NULL END)),GETDATE())
,b.*
--INTO DBATOOLS.dbo.Backup_Metrics
FROM
MainBigSet a
Left Join AlwaysOnInfo b
on a.name = b.database_name
group by
servername,
name,
Compatibility_level,
state_desc,
recovery_model_desc
,primary_replica,primary_recovery_health_desc,synchronization_health_desc,automated_backup_preference_desc,database_name,AGName
--order by name,backup_start_date desc
BUT, if i run it manually or in a SQL agent job as a tsql statement, it runs fine, only when i put it into a store procedure, or anything to do with SSIS... it never populates the information for Always On, just comes all nulls... but when i run it manually, whether tsql statement above OR store procedure... it works fine and collects the Always on information... any thoughts?
I would first review the permissions for the proxy account that calls the package.
You are reading the msdb database and master databases
October 7, 2020 at 1:35 pm
your absolutely right, thank you, so the problem is, and yes its msdb and master, but the following tables the user doesnt have access to read the data, but doesn't throw an error message, below are the tables the user needs, but not sure what permissions the user account needs, it already has read and execute on both msdb and master, plus I gave "View server state"... but still cant read the tables:
sys.dm_hadr_availability_group_states
master.sys.availability_groups
sys.availability_databases_cluster
October 7, 2020 at 1:45 pm
cool got it fixed, it was "View Definition" thank you for helping and pin pointing the location.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply