SQL SERVER – Scripts to Overview HADR / AlwaysOn Local Replica Server

SQL
5 Comments

Today, I have received a very interesting script from SQL Server Expert Dominic Wirth. He has written a very helpful script which displays a utilization overview of the local availability group replica server. The overview will contain the number of databases as well as the total size of databases (DATA, LOG, FILESTREAM) and is group by following two categories

  1. Replica role (PRIMARY / SECONDARY)
  2. Availability Group

Let us first see the script:

/*==================================================================
Script: HADR Local Replica Overview.sql
Description: This script will display a utilisation overview
of the local Availability Group Replica Server.
The overview will contain amount of databases as
well as total size of databases (DATA, LOG, FILESTREAM)
and is group by ...
1) ... Replica role (PRIMARY / SECONDARY)
2) ... Availability Group
Date created: 05.09.2018 (Dominic Wirth)
Last change: -
Script Version: 1.0
SQL Version: SQL Server 2014 or higher
====================================================================*/
-- Load size of databases which are part of an Availability Group
DECLARE @dbSizes TABLE (DatabaseId INT, DbTotalSizeMB INT, DbTotalSizeGB DECIMAL(10,2));
DECLARE @dbId INT, @stmt NVARCHAR(MAX);
SELECT @dbId = MIN(database_id) FROM sys.databases WHERE group_database_id IS NOT NULL;
WHILE @dbId IS NOT NULL
BEGIN
SELECT @stmt = 'USE [' + DB_NAME(@dbId) + ']; SELECT ' + CAST(@dbId AS NVARCHAR) + ', (SUM([size]) / 128.0), (SUM([size]) / 128.0 / 1024.0) FROM sys.database_files;';
INSERT INTO @dbSizes (DatabaseId, DbTotalSizeMB, DbTotalSizeGB) EXEC (@stmt);
SELECT @dbId = MIN(database_id) FROM sys.databases WHERE group_database_id IS NOT NULL AND database_id > @dbId;
END;
-- Show utilisation overview grouped by replica role
SELECT AR.replica_server_name, DRS.is_primary_replica AS IsPrimaryReplica, COUNT(DB.database_id) AS [Databases]
,SUM(DBS.DbTotalSizeMB) AS SizeOfAllDatabasesMB, SUM(DBS.DbTotalSizeGB) AS SizeOfAllDatabasesGB
FROM sys.dm_hadr_database_replica_states AS DRS
INNER JOIN sys.availability_replicas AS AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.databases AS DB ON DRS.group_database_id = DB.group_database_id
LEFT JOIN @dbSizes AS DBS ON DB.database_id = DBS.DatabaseId
WHERE DRS.is_local = 1
GROUP BY DRS.is_primary_replica, AR.replica_server_name
ORDER BY AR.replica_server_name ASC, DRS.is_primary_replica DESC;
-- Show utilisation overview grouped by Availability Group
SELECT AR.replica_server_name, DRS.is_primary_replica AS IsPrimaryReplica, AG.[name] AS AvailabilityGroup, COUNT(DB.database_id) AS [Databases]
,SUM(DBS.DbTotalSizeMB) AS SizeOfAllDatabasesMB, SUM(DBS.DbTotalSizeGB) AS SizeOfAllDatabasesGB
FROM sys.dm_hadr_database_replica_states AS DRS
INNER JOIN sys.availability_groups AS AG ON DRS.group_id = AG.group_id
INNER JOIN sys.availability_replicas AS AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.databases AS DB ON DRS.group_database_id = DB.group_database_id
LEFT JOIN @dbSizes AS DBS ON DB.database_id = DBS.DatabaseId
WHERE DRS.is_local = 1
GROUP BY AG.[name], DRS.is_primary_replica, AR.replica_server_name
ORDER BY AG.[name] ASC, AR.replica_server_name ASC;
GO

Here is the screenshot of the resultset which you will get if you run above script.

SQL SERVER - Scripts to Overview HADR / AlwaysOn Local Replica Server alwaysonreplica

There are many different kinds of reports which you can run via SQL Server Management Studio. However, sometimes the scripts simple as this script are very helpful and returns us quick results. You can further modify the above script to get additional details for your server as well.

Here are few additional scripts which also discusses the various concepts related to AlwaysOn.

If you have any other interesting script, please let me know and I will be happy to publish on the blog with due credit to you.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , ,
Previous Post
SQL SERVER – Rebuild Index Job Failed – Error: 9002 – The Transaction Log for Database ‘PinalDB’ is Full Due to ‘LOG_BACKUP’
Next Post
SQL Authority News – Microsoft Released SQL Server 2019 Preview

Related Posts

5 Comments. Leave new

  • I have to admit there is a small mistake in the header section. This script is only for SQL 2014 or higher because of the new column “is_primary_replica” in DMV “sys.dm_hadr_database_replica_states”.

    Reply
  • Running script I got the following errors

    Msg 1087, level 15, State 2, Line 32
    Must declare the table variable “@dbSizes”.

    Msg 1087, level 15, State 2, Line 44
    Must declare the table variable “@dbSizes”.

    But such variable is defined in row 16

    Reply

Leave a Reply

Menu