MSSQL Server’da Veritabanı ve Tablo Boyutunu Tabloya Kaydetmek

Veritabanı yönetiminde disk alanı kullanımı kritik bir konudur. Büyüyen sistemlerde alan kullanımı zamanla kontrolsüz hale gelebilir ve bu durum performans problemlerine ya da veri kaybına neden olabilir. Bu makalede, SQL Server üzerinde tüm veritabanlarının ve tabloların alan kullanım bilgilerini periyodik olarak izlemek ve kayıt altına almak için kullanılabilecek bir sistem anlatılmıştır.

Bu sistemde iki temel bileşen vardır:

1. DBA_DBSpaceused: Veritabanı düzeyindeki alan kullanım bilgilerini kaydeder.
2. DBA_DBTablespace: Her bir veritabanındaki tablo bazlı alan kullanım detaylarını kaydeder.

Veritabanı ve tablo bilgilerimizin kaydedileceği tablolarımızı oluşturalım.

Bu tablo, her bir veritabanının toplam veri ve log dosyası boyutlarını, doluluk oranlarını ve boş alanlarını kaydeder.

USE [DB_KAYIT]
GO

CREATE TABLE [dbo].[DBA_DBSpaceused] (
    [log_date] [smalldatetime] NULL,
    [database_name] varchar(50) NULL,
    [total_size_data] [bigint] NULL,
    [space_util_data] [bigint] NULL,
    [space_data_left] [bigint] NULL,
    [percent_fill_data] [float] NULL,
    [total_size_data_log] [bigint] NULL,
    [space_util_log] [bigint] NULL,
    [space_log_left] [bigint] NULL,
    [percent_fill_log] varchar(500) NULL,
    [total db size] [bigint] NULL,
    [total size used] [bigint] NULL,
    [total size left] [bigint] NULL
) ON [PRIMARY]

Bu tablo, her bir tablonun satır sayısı, kullanılan ve kullanılmayan alanları gibi bilgileri içerir.

USE [DB_KAYIT]
GO
CREATE TABLE [dbo].[DBA_DBTablespace] (
    [log_date]      smalldatetime NULL,
    [database_name] VARCHAR(128) NULL,
    [Schema_name]   VARCHAR(128) NULL,
    [Table_name]    VARCHAR(128) NULL,
    [row_counts]    BIGINT NULL,
    [used_MB]       DECIMAL(24, 4) NULL,
    [Unused_MB]     DECIMAL(24, 4) NULL,
    [Total_MB]      DECIMAL(24, 4) NULL
);

Aşağıdaki kod, SQL Server’daki her bir veritabanını dolaşır ve veri/log alanı kullanım bilgilerini hesaplayarak DBA_DBSpaceused tablosuna ekler:

/**** INSERT DB Size Info ***********/

insert into  DBA_DBSpaceused
EXEC master.sys.sp_MSforeachdb '
USE [?];

SELECT 
    GETDATE() AS [Tarih],
    x.[DATABASE NAME],
    x.[total size data],
    x.[space util],
    x.[total size data]-x.[space util] AS [space left data],
    x.[percent fill],
    y.[total size log],
    y.[space util],
    y.[total size log]-y.[space util] AS [space left log],
    y.[percent fill],
    y.[total size log]+x.[total size data] AS [total db size],
    x.[space util]+y.[space util] AS [total size used],
    (y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) AS [total size left]
FROM (
    SELECT 
        DB_NAME() AS [DATABASE NAME],
        SUM(CAST(size AS BIGINT))/128 AS [total size data],
        SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS BIGINT))/128 AS [space util],
        CASE 
            WHEN SUM(CAST(size AS BIGINT))/128 = 0 
            THEN ''divide by zero'' 
            ELSE SUBSTRING(
                CAST(
                    (SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS BIGINT))*1.0*100
                     / SUM(CAST(size AS BIGINT))
                    ) AS CHAR(50)),1,6
            ) 
        END AS [percent fill]
    FROM sys.master_files 
    WHERE database_id = DB_ID(DB_NAME())  
      AND type = 0
    GROUP BY type_desc  
) AS x
CROSS JOIN (
    SELECT 
        SUM(CAST(size AS BIGINT))/128 AS [total size log],
        SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS BIGINT))/128 AS [space util],
        CASE 
            WHEN SUM(CAST(size AS BIGINT))/128 = 0 
            THEN ''divide by zero'' 
            ELSE SUBSTRING(
                CAST(
                    (SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS BIGINT))*1.0*100
                     / SUM(CAST(size AS BIGINT))
                    ) AS CHAR(50)),1,6
            ) 
        END AS [percent fill]
    FROM sys.master_files 
    WHERE database_id = DB_ID(DB_NAME())  
      AND type = 1
    GROUP BY type_desc  
) AS y;
'

Bazı veritabanları kaydedilmiyorsa tür dönüşümlerinin doğru yapılması gerekmektedir.

Aşağıdaki sorgu, her bir tablonun kullandığı ve kullanmadığı alan miktarını MB cinsinden hesaplar.

/**** INSERT Table Size Info ***********/
insert into DBA_DBTablespace
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
SELECT 
getdate(),
''?'' dbname,
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ''?'' not in (''tempdb'', ''master'', ''msdb'')
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name'

Bu sorguların düzenli çalıştırılması için bir SQL Server Agent Job tanımlayarak günlük, haftalık veya saatlik periyotlarla bu verilerin kaydedilmesini sağlayabilirsiniz.

Toplanan veriler kullanılarak şu analizler yapılabilir:

• En fazla alan kullanan veritabanları
• Günlük/haftalık büyüme oranları
• Alan doluluk oranına göre alarm sistemleri

Bu verilerle Power BI ya da SSRS gibi araçlarla görsel raporlar da oluşturabilirsiniz.

Bu yapı sayesinde, SQL Server üzerindeki veritabanlarınızın ve tablolarınızın alan kullanım durumunu düzenli olarak izleyebilir, beklenmeyen büyümelere karşı erken önlem alabilirsiniz. Ayrıca geçmişe dönük veriler ile büyüme trendleri analiz edilerek kapasite planlaması yapılabilir.

Veritabanı boyutuna bakıp önceki haftalara göre ne kadar büyüdüğünü yüzdesel olarak hesaplayıp büyüme işlemini bulabiliriz.

Başka bir makalede görüşmek dileğiyle…

Sizin dostunuz (veliniz), ancak Allah, O’nun elçisi, rüku ediciler olarak namaz kılan ve zekatı veren mü’minlerdir. Maide Suresi, 55. Ayet