Post reply

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

  • 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

     

     

  • October 3, 2019 at 10:40 am

    #3685609 

    -- 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://qa.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

  • October 3, 2019 at 10:45 am

    #3685611

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

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

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