MSSQL Server Tüm Veritabanlarının ve Dosyaların Toplam Boyutu

Bu makalede tüm veritabanlarının toplam boyutunu ve dosya boyutlarını ele almış olacağız. Birinci komutumuz ilgili veritabanı altında bulunan mdf, ndf ve ldf dosyalarını ve boyutlarını görme komutunda. Database_id değeri 4’den büyük olan tüm veritabanlarını göstermektedir. Yorum satırınıda(–) dahil edersek belirtilen veritabanını getirmektedir. Aşağıdaki kodumuzda yorum satırı dahil değil

SELECT DB_NAME(database_id) AS database_name, 
    type_desc, 
    name AS FileName, 
    size * 8.0 / 1024/1024  AS CurrentSizeGB
FROM sys.master_files
WHERE database_id >=4 AND type IN (0,1) --and DB_NAME(database_id) like 'FILEGROUP'

mdf-ndf-ldf dosyalarımız gelmektedir.

Aşağıdaki komut ile instance altında bulunan tüm veritabanlarının boyutlarını görebiliriz.

SELECT DB_NAME(database_id) AS database_name, 
    sum(convert(bigint,size)* 8.0 / 1024/1024 ) AS CurrentSizeGB
FROM sys.master_files
WHERE database_id >=4 AND type IN (0) --and DB_NAME(database_id) like 'FILEGROUP'
group by DB_NAME(database_id)
order by CurrentSizeGB desc

Aşağıdaki komut ile mevcut olan instance üzerinde bulunan tüm database boyutunu ve ne kadar boş olan olduğuna dair ifadeyi aşağıdaki komutla öğrenebiliriz.

EXEC sp_MSforeachdb '
USE [?];
SELECT 
    DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size * 8.0 / 1024 / 1024 AS CurrentSizeGB, 
    CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) * 8.0 / 1024 / 1024 AS UsedSpaceGB,  
    (size - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)) * 8.0 / 1024 / 1024 AS FreeSpaceGB  
FROM sys.database_files
WHERE type IN (0,1);'

Aşağıdaki komut ile log dosyalarının boyutu ve ne kadar boş alan olduğunu görebiliriz.

EXEC sp_MSforeachdb '
USE [?];
SELECT 
    DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size * 8.0 / 1024 / 1024 AS CurrentSizeGB, 
    CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) * 8.0 / 1024 / 1024 AS UsedSpaceGB,  
    (size - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)) * 8.0 / 1024 / 1024 AS FreeSpaceGB  
FROM sys.database_files
WHERE type IN (1);'

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

Aşağıda bulunan bir diğer komut ise instance altında bulunan veritabanlarının dosya boyutlarını göstermektedir.

CREATE TABLE #FileSize
(dbName NVARCHAR(128), 
    FileName NVARCHAR(128), 
    type_desc NVARCHAR(128),
    CurrentSizeMB DECIMAL(10,2), 
    FreeSpaceMB DECIMAL(10,2)
);
    
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb 
'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0 AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
    
SELECT * 
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb','tempdb')
AND FreeSpaceMB > 0;
    
DROP TABLE #FileSize;

Bu makalede Sql Server Tüm veritabanlarının ve Dosyaların Toplam Boyutu komutlarını görmüş olduk.

Başka bir makalede görüşme dileğiyle.

“Ey İnananlar! Sabredin, düşmanlarınızdan daha sabırlı olun, cihada hazır bulunun, Allah’a karşı gelmekten sakının ki başarıya erişebilesiniz.” Âl-i İmran-200

Bir yanıt yazın