SQL Server’da Offline Veritabanları ve Orphaned Dosyalar: Nasıl Bulunur, Güvenle Nasıl Temizlenir?

Uzun süredir çalışan SQL Server ortamlarında, özellikle geliştirme ve test sunucularında, zamanla iki tür kalıntı birikir: kimsenin kullanmadığı halde offline durumda bekletilen veritabanları ve hiçbir veritabanına bağlı olmadığı halde diskte duran veri ve log dosyaları. Bu kalıntılar disk alanı tüketir, kapasite planlamasını yanıltır ve çoğu zaman gözden kaçan bir veri güvenliği riski oluşturur.

Bu rehberde offline veritabanlarının ve sahipsiz (orphaned) dosyaların nasıl oluştuğunu, nasıl tespit edileceğini ve veri kaybı riski olmadan nasıl temizlenebileceğini ele alıyoruz.

Orphaned Dosya Nedir ve Nasıl Oluşur?

Orphaned dosya, diskte bulunan ancak SQL Server instance’ına bağlı hiçbir veritabanı tarafından kullanılmayan veri (.mdf, .ndf) veya log (.ldf) dosyasıdır. Bu dosyalar genellikle şu durumlarda ortaya çıkar:

• Offline durumdaki bir veritabanının silinmesi. Microsoft’un dokümantasyonuna göre bir veritabanı ya da dosyalarından herhangi biri offline durumdayken DROP DATABASE çalıştırılırsa, veritabanı instance’tan kaldırılır ancak diskteki dosyalar silinmez. Bir veritabanını önce offline’a alıp bir süre bekledikten sonra silmek yaygın bir alışkanlık olduğu için, orphaned dosyaların en sık nedeni budur.
• Detach edilen veritabanları. sp_detach_db veya SSMS’teki Detach işlemi veritabanını instance’tan ayırır, ancak dosyaları bilerek diskte bırakır. Daha sonra yeniden attach edilmeyen veritabanlarının dosyaları bu şekilde sahipsiz kalır.
• Taşıma ve geri yükleme işlemleri. Bir veritabanı farklı bir klasöre restore edildiğinde veya taşındığında, eski konumdaki dosyalar ya da işlem öncesinde alınmış manuel kopyalar çoğu zaman unutulur.
• Kaldırılan instance’lar. Bir SQL Server instance’ı kaldırıldığında kullanıcı veritabanlarının dosyaları diskte kalabilir.

Neden Önemsenmeli?

Offline veritabanları ve orphaned dosyalar ilk bakışta yalnızca bir disk alanı sorunu gibi görünse de etkileri daha geniştir:

• Disk alanı ve kapasite planlaması. Büyük veritabanlarından kalan dosyalar onlarca, hatta yüzlerce gigabayt yer kaplayabilir; bu da disk doluluk alarmlarının yanlış yorumlanmasına ve gereksiz depolama yatırımlarına yol açar.
• Veri güvenliği. Orphaned bir .mdf dosyası, silinmiş bir veritabanının verilerini olduğu gibi içerir. Bu dosya başka bir sunucuya kopyalanıp attach edildiğinde, SQL Server’daki yetki tanımlarının hiçbiri onu korumaz. Kişisel veri içeren sistemlerde bu durum KVKK açısından da göz ardı edilmemesi gereken bir risktir. Bu tür dosyaların veri envanterine ve imha sürecine nasıl dahil edileceğini veri yönetişimi rehberimizde ele aldık.
• Operasyonel karmaşa. Offline veritabanları, tüm veritabanlarını dolaşan bazı bakım ve izleme script’lerinde hatalara ya da gereksiz uyarılara neden olabilir; envanter raporlarında da gerçekte kullanılmayan sistemlerin aktifmiş gibi görünmesine yol açar.

Offline Veritabanlarını Bulma

Aşağıdaki sorgu, instance üzerindeki offline veritabanlarını dosya yolları, boyutları ve son tam yedek tarihleriyle birlikte listeler. Son yedek tarihi, veritabanının ne kadar süredir kullanılmadığına dair fikir verir ve temizlik öncesinde güncel bir yedeğin bulunup bulunmadığını gösterir:

SELECT d.name                                   AS database_name,
       SUSER_SNAME(d.owner_sid)                 AS owner_name,
       mf.type_desc                             AS file_type,
       mf.physical_name,
       CAST(mf.size AS bigint) * 8 / 1024       AS size_mb,
       (SELECT MAX(b.backup_finish_date)
          FROM msdb.dbo.backupset AS b
         WHERE b.database_name = d.name
           AND b.type = 'D')                    AS last_full_backup
FROM sys.databases AS d
JOIN sys.master_files AS mf
     ON mf.database_id = d.database_id
WHERE d.state_desc = N'OFFLINE'
ORDER BY d.name, mf.type_desc;

Bu listedeki her veritabanı için sorulması gereken soru, veritabanının neden offline’a alındığı ve hâlâ birinin ona ihtiyaç duyup duymadığıdır. Bu sorunun cevabını genellikle yalnızca uygulama sahibi verebilir.

Orphaned Dosyaları Bulma

Orphaned dosyaları tespit etmenin güvenilir yolu, sunucudaki tüm SQL Server instance’larının kullandığı dosyaların listesini almak ve bu klasörlerdeki dosyalarla karşılaştırmaktır. Yalnızca tek bir instance’a bakmak yanıltıcı olabilir: aynı sunucuda birden fazla instance varsa, birinin dosyası diğerine göre “sahipsiz” görünür.

Aşağıdaki PowerShell script’i, SqlServer modülündeki Invoke-Sqlcmd komutunu kullanarak belirttiğiniz instance’ların dosya listesini ve varsayılan veri/log klasörlerini alır, ardından bu klasörlerde hiçbir instance’a ait olmayan veri ve log dosyalarını listeler:

# Bu sunucudaki TÜM instance'ları listeleyin
$instances = @('SQLHOST', 'SQLHOST\INST2')

$known   = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)
$folders = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)

foreach ($i in $instances) {
    Invoke-Sqlcmd -ServerInstance $i -Query "SELECT physical_name FROM sys.master_files;" |
        ForEach-Object {
            [void]$known.Add($_.physical_name)
            [void]$folders.Add((Split-Path $_.physical_name -Parent))
        }
    Invoke-Sqlcmd -ServerInstance $i -Query "SELECT CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(260)) AS p
                                             UNION
                                             SELECT CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS nvarchar(260));" |
        ForEach-Object { [void]$folders.Add($_.p.TrimEnd('\')) }
}

Get-ChildItem -Path @($folders) -File -ErrorAction SilentlyContinue |
    Where-Object { $_.Extension -in '.mdf', '.ndf', '.ldf' -and -not $known.Contains($_.FullName) } |
    Select-Object FullName,
                  @{ n = 'SizeGB'; e = { [math]::Round($_.Length / 1GB, 2) } },
                  LastWriteTime |
    Sort-Object SizeGB -Descending

Script yalnızca SQL Server’ın bildiği klasörlere bakar. Farklı klasörlere kopyalanmış dosyaları da bulmak isterseniz ilgili yolları $folders listesine elle ekleyebilirsiniz. Açık kaynaklı dbatools modülündeki Find-DbaOrphanedFile komutu da benzer bir kontrol için kullanılabilir.

Güvenli Temizlik Süreci

Offline veritabanları için

Kullanılmadığı teyit edilen bir offline veritabanını kaldırmadan önce, ihtiyaç halinde geri dönebilmek için son bir yedek almanızı ve veritabanını online durumdayken silmenizi öneriyoruz:

1. Veritabanının artık kullanılmadığını uygulama sahibiyle yazılı olarak teyit edin.
2. Veritabanını online duruma getirin.
3. Checksum ile son bir tam yedek alın ve yedeğin okunabilir olduğunu doğrulayın.
4. Bu yedeği, kurum politikanızda belirlenen süre boyunca saklayın.
5. Veritabanını online durumdayken silin; böylece fiziksel dosyalar da kaldırılır.

ALTER DATABASE [OldSalesDB] SET ONLINE;

BACKUP DATABASE [OldSalesDB]
  TO DISK = N'backup-sharearchiveOldSalesDB_final.bak'
  WITH CHECKSUM, COMPRESSION;

RESTORE VERIFYONLY
  FROM DISK = N'backup-sharearchiveOldSalesDB_final.bak'
  WITH CHECKSUM;

DROP DATABASE [OldSalesDB];

Dosyalarından biri eksik olduğu için online duruma getirilemeyen bir veritabanını yine silebilirsiniz; ancak bu durumda kalan dosyaların ayrıca elle temizlenmesi gerekir.

Orphaned dosyalar için

Hiçbir instance’a bağlı olmayan dosyalarda ise kalıcı silme yerine kademeli bir yaklaşım öneriyoruz:

1. Dosyanın sunucudaki hiçbir instance tarafından kullanılmadığını ve bilerek saklanan bir kopya olmadığını doğrulayın.
2. İçeriğinden emin değilseniz, dosyayı bir test instance’ına CREATE DATABASE ... FOR ATTACH ile bağlayarak inceleyin.
3. Dosyaları hemen silmek yerine, erişimi kısıtlanmış bir karantina klasörüne taşıyın ve önceden belirlenmiş bir süre, örneğin 30 gün, bekleyin.
4. Bu süre içinde bir ihtiyaç ortaya çıkmazsa dosyaları kalıcı olarak silin ve yapılan işlemi kayıt altına alın.

Sorunu Kaynağında Önlemek

Orphaned dosyaların büyük bölümü, veritabanı kaldırma işlemleri için belirlenmiş bir yöntem olmamasından kaynaklanır. Bir veritabanını devreden çıkarırken her seferinde aynı adımların izlenmesi, yani son yedeğin alınıp doğrulanması ve veritabanının online durumdayken silinmesi, bu sorunun tekrar etmesini büyük ölçüde engeller. Veritabanlarını uzun süre offline durumda bekletmek yerine, karar verildiğinde yedekleyip kaldırmak hem daha temiz hem de daha güvenli bir yaklaşımdır. Bunun yanında offline veritabanı ve orphaned dosya kontrolünü aylık bakım rutinlerinize eklemenizi öneriyoruz.