Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Магический скрипт для большой информативности  [new]
Den_KP
Member

Откуда:
Сообщений: 211
Добрый день друзья!

Нужна Ваша помощь сделал скрипт с отображением баз Always On
select
    ag.name as Always_name,
    ar.replica_server_name,
    d.name as [database_name],
	d.state_desc as [database_status],
    hars.synchronization_state_desc as synchronization_state,
    hars.synchronization_health_desc as synchronization_health,
    hars.database_state_desc as db_state,
    hars.last_commit_time
from 
sys.dm_hadr_database_replica_states as hars
join sys.availability_replicas as ar
    on hars.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = hars.group_id
join sys.databases as d
    on d.group_database_id = hars.group_database_id
order by Always_name, replica_server_name


1. Не могу дотумкатся как вытащить прям все базы даже без Always On
2. В db_state сделать условие чтобы менялся текст NULL на Secondary ONLINE на Primary
3. Доклеить 1 поле с Extended_Properties
5 фев 18, 08:58    [21165589]     Ответить | Цитировать Сообщить модератору
 Re: Магический скрипт для большой информативности  [new]
Den_KP
Member

Откуда:
Сообщений: 211
Den_KP
Добрый день друзья!

Нужна Ваша помощь сделал скрипт с отображением баз Always On
select
    ag.name as Always_name,
    ar.replica_server_name,
    d.name as [database_name],
	d.state_desc as [database_status],
    hars.synchronization_state_desc as synchronization_state,
    hars.synchronization_health_desc as synchronization_health,
    hars.database_state_desc as db_state,
    hars.last_commit_time
from 
sys.dm_hadr_database_replica_states as hars
join sys.availability_replicas as ar
    on hars.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = hars.group_id
join sys.databases as d
    on d.group_database_id = hars.group_database_id
order by Always_name, replica_server_name


1. Не могу дотумкатся как вытащить прям все базы даже без Always On
2. В db_state сделать условие чтобы менялся текст NULL на Secondary ONLINE на Primary
3. Доклеить 1 поле с Extended_Properties


Для Опций есть отдельный скрипт
-----SELECT Logs-------------
IF OBJECT_ID('tempdb.dbo.#dbdetailes', 'U') IS NOT NULL
DROP TABLE #dbdetailes;
create table #dbdetailes
(database_name varchar(200)
,log_size_mb varchar(200)
,row_size_mb varchar(200)
,total_size_mb varchar(200)
)
insert into #dbdetailes
SELECT
database_name = DB_NAME(database_id)
, CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as log_size_mb
, CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as row_size_mb
, CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) as total_size_mb
FROM sys.master_files
GROUP BY database_id
--------tempdb for extended properties--------------
IF OBJECT_ID('tempdb.dbo.#properties', 'U') IS NOT NULL
DROP TABLE #properties;
CREATE TABLE #properties
(DatabaseName nvarchar(200) ,
PropertyName nvarchar(200) ,
PropertyValue nvarchar(200) )

EXEC sp_MSforeachdb
'INSERT INTO #properties SELECT ''?'' AS DatabaseName,
CAST(name AS nvarchar), CAST(Value AS nvarchar)
FROM [?].sys.extended_properties WHERE class=0'
--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbis', 'U') IS NOT NULL
DROP TABLE #dbis;
create table #dbis
(DatabaseName nvarchar(200)
,InformationSystem nvarchar(200)
)
insert into #dbis
select
DatabaseName
,PropertyValue as InformationSystem
from #properties
where PropertyName like 'Information System'

--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbru', 'U') IS NOT NULL
DROP TABLE #dbru;
create table #dbru
(DatabaseName nvarchar(200)
,ResponsibleUnit nvarchar(200)
)
insert into #dbru
select
DatabaseName
,PropertyValue as ResponsibleUnit
from #properties
where PropertyName like 'Responsible Unit'

--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbrp', 'U') IS NOT NULL
DROP TABLE #dbrp;
create table #dbrp
(DatabaseName nvarchar(200)
,ResponsiblePerson nvarchar(200)
)
insert into #dbrp
select
DatabaseName
,PropertyValue as ResponsiblePerson
from #properties
where PropertyName like 'Responsible Person'
-------Result----------------
select
@@servername AS Instance
,s.DatabaseName as DatabaseName
,s.InformationSystem as InformationSystem
,u.ResponsibleUnit as ResponsibleUnit
,p.ResponsiblePerson as ResponsiblePerson
,d.log_size_mb as log_size_mb
,d.row_size_mb as row_size_mb
,d.total_size_mb as total_size_mb

from #dbis s
join #dbru u on s.DatabaseName = u.DatabaseName
join #dbrp p on s.DatabaseName = p.DatabaseName
join #dbdetailes d on s.DatabaseName = d.database_name
5 фев 18, 09:07    [21165595]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить