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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy