November 28, 2017 at 1:27 am
Hello,
I have a question:
We are hosting a very big enviroment, and we want to gather SQL-Server database information by Query for our reports.
This is no problem since we have sys.sysdatabases available in SQL-Server 2012 and newer versions.
The problem that I have is:
How can I write a query that will tell me the following:
DBName, Database and log file-size. And if it is primary or secondairy, when we use Always_on?
I know that we need to start by sys.sysdatabases, but I can not find the information about Always_on.
Thanks for your help!
Best regards,
Mischa
November 28, 2017 at 7:26 am
Found a solution, hopefully someone else can use this also:
select
d.name, d.database_id, d.compatibility_level, d.state, recovery_model_desc,
case
when h.role_desc is NULL then 'Stand-Alone'
when h.role_desc = 'secondary' then 'always_on secondary'
when h.role_desc = 'primary' then 'always_on primary'
else '<UNKNOWN>'
end as Role,
(SELECT SUM((m.size*8)/1024) FROM sys.master_files m WHERE m.database_id = d.database_id AND m.type_desc = 'rows') AS Data_MB,
(SELECT SUM((m.size*8)/1024) FROM sys.master_files m WHERE m.database_id = d.database_id AND type_desc = 'log') AS Log_MB
from sys.databases d
left join sys.dm_hadr_availability_replica_states h on h.replica_id = d.replica_id
order by d.name
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply