MSSQL Server Data Dosyası Üzerinden Shrink İşlemi

Bazen büyük veritabanları üzerinde bazı tabloları silme gereği duyarız. Sildiğimiz bu veritabanı tablosu çok büyük boyutlardaysa bu alanı diske iade etmek isteriz. ilgili tabloyu silmeden önce tablonun hangi file group üzerinde olduğunun bulunması gerekmektedir. Bunun sebebi shrink işleminin hangi filegroup üzerinden olduğunu görüp shrink işlemini ilgili filegroup’a göre yapmamız gerekmektedir. Aşağıdaki komut ile veritabanı tablolarının hangi filegroup üzerinde olduğu bulabiliriz.

Aşağıdaki komut ile veritabanı altında bulunan partition tablo ve partition sız tabloları getirmektedir. Aynı zamanda tüm tabloların filegroupları görülmektedir. Çünkü sileceğimiz tablonun filegroup’un bilinmesi gerekmektedir. Yoksa Hangi filegroup üzerinde işlem yapacağımızı göremeyiz.

SELECT 
    SCHEMA_NAME(t.schema_id) AS [Schema],
    t.name AS [Table Name],
    CASE 
        WHEN ps.data_space_id IS NOT NULL THEN 'YES'
        ELSE 'NO'
    END AS [Is Partitioned],
    ISNULL(ps.name, 'N/A') AS [Partition Scheme],
    ISNULL(pf.name, 'N/A') AS [Partition Function],
    -- Filegroup bilgisi (partition olmayanlar için)
    CASE 
        WHEN ps.data_space_id IS NULL THEN fg.name 
        ELSE 'See Partition Details Below'
    END AS [Filegroup (Non-Partitioned)],
    -- Partition detayları (partition olanlar için)
    p.partition_number AS [Partition Number],
    pfg.name AS [Partition Filegroup],
    p.rows AS [Rows in Partition],
    prv.value AS [Partition Boundary Value]
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1) -- Heap or Clustered Index
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_id AND ps.data_space_id IS NULL
LEFT JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id AND p.partition_number = dds.destination_id
LEFT JOIN sys.filegroups pfg ON dds.data_space_id = pfg.data_space_id
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id 
    AND p.partition_number = CASE pf.boundary_value_on_right 
        WHEN 1 THEN prv.boundary_id + 1 
        ELSE prv.boundary_id 
    END
WHERE t.is_ms_shipped = 0 -- Only user-created tables
ORDER BY [Schema], [Table Name], [Partition Number];

Tablomuz belirlenip silindikten sonra tablomuzun bulunduğu data file üzerinde shrink işlemi yapılmaktadır.

Aşağıdaki komut ile Filegroup üzerinde boşluk oranını görmekteyiz. Makalenin sonunda tüm instance altında bulunan tüm veritabanlarının içerisinde bulunan filegroup boşluk oranları görülebilir.

SELECT
FILEGROUP_NAME(data_space_id) as FilegroupName,
SUM(size/128.0) AS CurrentSizeMB, 
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS FreeSpaceMB,
(
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) / -- FreeSpaceMB
    (SUM(size/128.0))  -- CurrentSizeMB
    * 100 -- Convert to percentage
) AS FilegroupPercentFree,
COUNT(*) as NumberOfFilesInFilegroup
FROM sys.database_files
WHERE data_space_id <> 0
GROUP BY FILEGROUP_NAME(data_space_id);

FG2023 datafile üzerinde shrink işlemini yapmış olacağız. Bu işlemin sürekli belirli aralıklarla yapılması için aşağıdaki kodu kullanabiliriz.

Kodumuzdan önce belirlenen filegroup’un hangi file_id değerine sahip olduğunu görmek için aşağıdaki komut kullanılır.

use DB_NAME
SELECT file_id,type,type_desc,data_space_id,name,physical_name,size FROM sys.database_files

Data dosyasının type’ı 0, log dosyasının type’ı 1’dir. İlgili filegroup’un file_id değeri öğrenilir. Aşağıdaki komut log dosyası için yapılacaksa dikkat edilmesi gerekmektedir. fileid 9 üzerinden işlem yapacağımız için belirlenmektedir.

DECLARE @DatabaseName NVARCHAR(128) = 'DB_NAME'; -- Shrink yapılacak veritabanı adı
DECLARE @TargetSizeMB INT = 3600000; -- Hedeflenen boyut (MB)
DECLARE @ShrinkIncrementMB INT = 512000; -- Her seferinde küçültülecek boyut (MB)
DECLARE @CurrentSizeMB INT;
DECLARE @LogicalFileName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

-- Veritabanının File Group altında işlem yapılacaksa ilgili file_id belirlenir
SELECT @CurrentSizeMB = SUM(size/128)
FROM sys.master_files
WHERE database_id = DB_ID(@DatabaseName) AND type = 0 AND file_id=9; 
select @CurrentSizeMB


SELECT @LogicalFileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@DatabaseName) AND type = 0 AND file_id = 9; 
select @LogicalFileName


-- --Kademeli shrink işlemi
WHILE @CurrentSizeMB > @TargetSizeMB
BEGIN
   SET @CurrentSizeMB = @CurrentSizeMB - @ShrinkIncrementMB;
    
   -- Hedef boyut minimum hedefin altına düşmesin
    IF @CurrentSizeMB < @TargetSizeMB
        SET @CurrentSizeMB = @TargetSizeMB;
    SET @SQL = N'USE [' + @DatabaseName + N'];  DBCC SHRINKFILE (N''' + @LogicalFileName + N''', ' + CAST(@CurrentSizeMB AS NVARCHAR(20)) + ');';
    EXEC sp_executesql @SQL;

    WAITFOR DELAY '00:30:00'; -- 30 dakika bekle, Eklenmesede olur
END

Not: Bazı arkadaşlar size değerini neden 128’e böldüğümüzü söyleyebilir. Bunun sebebi normalde 1 mb normalde 1024 kb olduğu için bir page 8 kb olduğundan dolayı 1 MB’ın 128 page’e karşılık geldiğini ifade ederiz. page count 128’e bölünmesi toplam page’e bölünmesi demektir.

Yukarıdaki komut bir job aracılığıyla belirli aralıklarla çalıştırılmaktadır. Yukarıdaki komutumuz çalıştıktan sonra hangi tablo shrink yapılan filegroupta bulunmuşsa ilgili tabloların rebuild reorganize yapılması gerekmektedir.

Yukarıdaki komutumuzda while döngüsü sürekli dönmez. İlgili shrink file ifadesi çalıştıktan sonra bir sonraki değeri set ederek file yeni değeri shrink işlemine tabi tutmaktadır.

Tablomuz ister normal bir tablo olsun ister partition’lı bir tablo olsun rebuild ve reorganize işlemleri yapılabilir.

Aşağıdaki komutlar normal bir tablo için index rebuild ve organize işlemlerini yapabiliriz.

-- Tek bir indeksi rebuild etmek için

ALTER INDEX [index_name] ON [table_name] REBUILD;

ALTER INDEX Index_Name ON Table_Name REBUILD WITH (FILLFACTOR = 70,ONLINE=10)

-- Tek bir indeksi reorganize etmek için
ALTER INDEX [index_name] ON [table_name] REORGANIZE WITH ( ONLINE=10 );

Tablolarımız partition yapısında ise aşağıdaki komutlar kullanılmaktadır. Partition numaraları makalenin başındaki komuttan öğrenilebilir.

ALTER INDEX [Index_Adı] ON [Schema].[Tablo_Adı] 
REBUILD PARTITION = [Partition_Numarası]
WITH (ONLINE = ON, MAXDOP = 4, FILLFACTOR = 90);

ALTER INDEX [Index_Adı] ON [Schema].[Tablo_Adı] 
REORGANIZE PARTITION = [Partition_Numarası];

Bir başka yöntem ise ikinci bir data dosyası aynı filegroup altına eklenir. Eklendiğinde eklenen ifadede shrink işlemine dahil olmaktadır. Empty file yöntemi ile ikinci oluşturulan data dosyasının üzerine ilk data dosyası shrink işlemine tabi tutulur.

Aşağıdaki komut tüm instance altında bulunan veritabanlarındaki filegroupların doluluk oranını göstermektedir.

exec sp_MSforeachdb'

use [?]
  SELECT
        DB_NAME() AS VeritabaniAdi,
        fg.name AS FilegroupName,
        SUM(df.size) / 128.0 AS CurrentSizeMB,
        SUM(df.size - CAST(FILEPROPERTY(df.name, ''SpaceUsed'' ) AS INT)) / 128.0 AS FreeSpaceMB,
        (SUM(df.size - CAST(FILEPROPERTY(df.name, ''SpaceUsed'' ) AS INT)) * 100.0) / SUM(df.size) AS FilegroupPercentFree,
        COUNT(*) AS NumberOfFilesInFilegroup
    FROM sys.database_files df
    INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
    WHERE df.type_desc = ''ROWS''
    GROUP BY fg.name;'

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

“Şüphesiz peygamberlerimize ve iman edenlere, hem dünya hayatında, hem şahitlerin şahitlik edecekleri günde yardım ederiz.” Mü’min Suresi; 51. Ayet

Bir yanıt yazın