Tüm Instance’lardaki Tüm Veritabanlarının Toplam Boyutu

Instance sayınız ve veritabanı sayınız arttığında tüm veritabanlarınızın toplam boyutunu bulmak biraz zorlaşabilir. Bazen yöneticiniz sql server veritabanlarının toplam boyutu hakkında sizden bilgi isteyebilirler. Böyle bir durumda eğer instance sayınız azsa ve high availability çözümlerinden birini kullanmıyorsanız aşağıdaki script’i kullanarak instance bazında tüm veritabanlarının toplam boyutunu bulabilirsiniz.

with fs
as
(
    select database_id, type, size * 8.0 / 1024/1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) +
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) DBSizeGB
from sys.databases db
where (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) is not null
order by DBSizeGB desc

Mesela benim çalıştırdığım instance’da yukardaki script’in sonucunda 30 kayıt geldi. Ama unutmayalım bu script standalone ve always on’un secondary veritabanlarının hepsini içeriyor.

Bazı sistemlerde always on kullanan instance’ların içersinde always on’a dahil olmayan veritabanları da oluyor. Bu yüzden bu script’i hem primary hem de secondary instance’da çalıştırmanız gerekir. Böyle olduğunda da always on’a dahil veritabanlarının boyutunu 2 kere hesaplamış oluyorsunuz.

Script’i aşağıdaki şekle çevirince o instance üzerindeki aktif tüm veritabanlarının(always on’a dahil bir veritabanıysa primary olduğu takdirde listeye giriyor. Always On’a dahil olmayan(standalone) veritabanları da listeye giriyor.) isimleri ve büyüklüklerini listeliyor.

with fs
as
(
    select database_id, type, size * 8.0 / 1024/1024 size
    from sys.master_files where database_id IN
       (
       SELECT DISTINCT
DB_ID(dbcs.database_name) AS veritabaniid
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 1
union all
select DB_ID(name) veritabaniid from sys.databases where replica_id is null and database_id >4
       )
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) +
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) DBSizeGB
from sys.databases db
where (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) is not null
order by DBSizeGB desc

Yukardaki script kısmen işimizi görür. Elimizde bütün instance’larda (içinde always on’a dahil olan veritabanı olsun olmasın) bu script’i çalıştırıp tüm veritabanlarının boyutunu toplayabiliriz. Yukardaki script’in çalışması sonucundaki ekran görüntüsünü aşağıda bulabilirsiniz. Gördüğünüz gibi 12 kayıt geldi. Demekki 30 veritabanından 18 tanesi ya always on’un secondary veritabanıymış ya da sistem veritabanıymış(bu script’te sistem veritabanlarını da çıkarıyoruz) Bu script’i always on kullanan instance’larda hem primary hem de secondary instance’da çalıştırıp çıkan sonucu toplamalısınız.

İşimizi biraz daha kolaylaştıralım. O instance üzerindeki veritabanlarını manual olarak toplamak yerine bir cursor içersinde döndürerek sql server’a toplattıralım. Aşağıdaki script yardımıyla da bir instance üzerindeki tüm veritabanlarının(always on’a dahil bir veritabanıysa primary olduğu takdirde listeye giriyor. Always On’a dahil olmayan(standalone) veritabanları da listeye giriyor.) toplam boyutunu veriyor.

DECLARE @size int
DECLARE @totalsize int
set @totalsize=0
DECLARE db_cursor CURSOR FOR 
with fs
as
(
    select database_id, type, size * 8.0 / 1024/1024 size
    from sys.master_files where database_id IN
       (
       SELECT DISTINCT
DB_ID(dbcs.database_name) AS veritabaniid
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 1
union all
select DB_ID(name) veritabaniid from sys.databases where replica_id is null and database_id >4
       )
)
select    
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) +
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) DBSizeGB
from sys.databases db
where (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) is not null
order by DBSizeGB desc
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @size  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @totalsize= @totalsize+@size
       FETCH NEXT FROM db_cursor INTO @size  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor
select @totalsize

Yukardaki script’in sonucunun ekran çıktısınıda aşağıdaki ekran görüntüsünde görebilirsiniz. Bir önceki script’te çıkan sonuçları toplayarak o instance üzerindeki toplam boyutu GB türünden veriyor.

Peki benim birden fazla 10 tane ya da daha fazla instance’ım varsa tek tek bu instance’lara bağlanarak bu script’i çalıştırmam mı gerekiyor?

Hayır. Bütün instance’larınızı Registered Servers’a kaydererek tek bir seferde hepsi için hesaplatabilirsiniz. Registered Servers ile ilgili detaylara “Registered Server ile Birden Fazla Instance Üzerinde Aynı Script’i Çalıştırmak” isimli makalemden ulaşabilirsiniz.

Registered Servers’a aşağıdaki gibi sağ tıklayarak new query diyerek yukardaki sorguyu çalıştırdığınızda tek bir seferde tüm instance’larınızdaki veritabanı boyutlarınızın toplamını bulabilirsiniz. Tabi bu sorguyu registered server ile çalıştırdığınız da registered server’ınızda kaç tane instance varsa o kadar satır dönecektir. O satırların hepsini toplayarak toplam veritabanı boyutunuzu bulabilirsiniz.