Need report for DBSize,DB Status,Backup Status,Always on for multiple servers

Question

Need report for DBSize,DB Status,Backup Status,Always on for multiple servers

vishalsurya50

SSC Eights!

Points: 907
More actions
October 3, 2019 at 8:14 am

#3685590

Need SQL Dashboard report in HTML or any format for the below
1)DB Size
2)Backup size
3)Always on status
4)Job failures
5)Server up time

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 1

-- DB Size
SELECT 
database_name = DB_NAME(database_id)
, status = state_desc
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id, state_desc

-- Backup size (Refer below thread and see if it's relevant)
https://www.sqlservercentral.com/forums/topic/how-to-check-the-log-of-export-import-database-wizard#post-3683181

-- Always on status
DECLARE @HADRName varchar(25)
SET @HADRName = @@SERVERNAME
select n.group_name,n.replica_server_name,n.node_name,rs.role_desc,
db_name(drs.database_id) as 'DBName',drs.synchronization_state_desc,drs.synchronization_health_desc
from sys.dm_hadr_availability_replica_cluster_nodes n
join sys.dm_hadr_availability_replica_cluster_states cs
on n.replica_server_name = cs.replica_server_name
join sys.dm_hadr_availability_replica_states rs 
on rs.replica_id = cs.replica_id
join sys.dm_hadr_database_replica_states drs
on rs.replica_id=drs.replica_id
where n.replica_server_name <> @HADRName

-- Job failures (Refer below thread and see if it's relevant)
https://www.sqlshack.com/reporting-and-alerting-on-job-failure-in-sql-server/

-- Server up time (Below query will give you when the sqlserver was last started). 
-- You can use DATEDIFF function to get the difference bwtween SQL Server Start Time and GETDATE()
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

vishalsurya50 SSC Eights! Points: 907 More actions · Answer 2

Thanks for your reply. Sorry Buddy. I need the output to be stored in HTML format or the output should create an excel & store automatically in it.I dont need the script for each one as i have it already..