SQL Server sistem veritabanları ne işe yarar?

SQL Server’da tempdb disk üzerinde veri ve log dosyaları kullanır. Yalnız RAM’de yaşayan bir veritabanı gibi düşünmek, disk doluluğunu ve sorguların oluşturduğu geçici yükü gözden kaçırmanıza neden olur. Bu yazının eski sürümündeki açıklamayı önce buradan düzeltiyorum.

Sistem veritabanlarının her biri farklı bir iş üstleniyor. model yeni veritabanlarına başlangıç ayarlarını verirken msdb zamanlanmış işler ve yedek geçmişi gibi yönetim bilgilerini tutuyor. Bu yazıda görevlerini ayırıp, çalışan sunucuda veri değiştirmeden kullanabileceğiniz birkaç sorguya bakacağız.

Hangi veritabanı neyi saklıyor?

Microsoft’un sistem veritabanları belgesinde SQL Server için beş temel veritabanı açıklanır. Management Studio’da normal listeyi açtığınızda bunların hepsini aynı biçimde görmezsiniz.

Resource, gizli bir sistem veritabanıdır. Sistem nesneleri mantıksal olarak veritabanlarının sys şemasında görünür, fiziksel tanımları Resource içinde tutulur. Normal bir uygulama veritabanı gibi tablo ekleyip çıkardığınız alan değildir.

Bu anlatım SQL Server içindir. Azure SQL Database’in tek veritabanı modeli, SQL Server Agent ve sistem veritabanları açısından aynı yönetim yüzeyini sunmaz. Azure SQL Managed Instance daha yakın bir model kullanır ama hizmete özgü kısıtlar ayrıca değerlendirilir.

master yedeği neden ayrı düşünülür?

master, sunucu düzeyindeki login kayıtları ve veritabanlarının konumları gibi bilgileri barındırır. Yalnız uygulama veritabanının yedeğine sahip olmanız, yeni sunucuda bütün sunucu ayarlarını geri getirdiğiniz anlamına gelmez. Kurtarma planında bu ayrımı açık tutun.

Master veritabanı yedekleme yazısında bu konunun temelini anlatmıştım. Geri yüklemede SQL Server sürümü ve güncelleme seviyesi gibi koşulları ayrıca kontrol etmek gerekir. Üretimde ilk denemeyi yapmak yerine uyumlu bir test ortamında kurtarma sırasını prova edin.

Login ile veritabanı kullanıcısını da birbirine karıştırmayın. Sunucuya giriş kimliği ile belirli veritabanındaki yetkilendirme farklı katmanlardır. Bir kullanıcı veritabanını başka sunucuya taşıdığınızda kimlik eşleşmesini kontrol etmek gerekebilir.

model üzerinde yapılan değişiklik nereye yayılır?

model değişiklikleri sonradan oluşturulan veritabanlarına taşınır. Mevcut veritabanlarını geriye dönük topluca değiştirmez. Varsayılan dosya boyutları ve recovery model gibi seçenekler burada belirlenebilir.

Eski yazıdaki aşağıdaki örnekte model içine eklenen tablonun yeni veritabanında da oluşması gösteriliyordu. Teknik olarak bu davranış mümkündür, ancak uygulamaya özel nesneleri üretimdeki model içine koymayı genel kurulum yöntemi olarak önermiyorum.

Bir uygulamanın tarih tablosunu veya fonksiyonunu burada tanımlarsanız, ilgisiz yeni veritabanlarına da taşırsınız. Uygulamaya ait tabloları sürümlenen kurulum veya migration betikleriyle yönetmek daha görünür bir yöntem. Sunucunun ortak başlangıç ayarıyla uygulamanın şemasını ayırmış olursunuz.

Mevcut recovery model değerini değiştirmeden görmek için şu sorguyu kullanabilirsiniz:

SELECT name, recovery_model_desc, collation_name
FROM sys.databases
WHERE name IN ('master', 'model', 'msdb', 'tempdb');

Sonuçları okumadan önce hangi sunucuya bağlı olduğunuzu kontrol edin. Aynı isimli sistem veritabanları bütün instance’larda bulunur, geliştirme ve üretim bağlantılarını karıştırmak kolaydır. Management Studio bağlantı adını kaydetmeniz yeterli bir başlangıç.

msdb yalnız job listesinden ibaret değil

SQL Server Agent işlerinizin tanımları ve zamanlamaları msdb içinde tutulur. Yedekleme geçmişi ve Database Mail bilgileri de buradadır. Uygulama veritabanının sağlam olması, gece çalışan yedekleme işinizin tanımının da güvende olduğu anlamına gelmez.

Eski ekrandaki job tanımı aynı temel ilişkiyi gösteriyor. Yalnız sysschedules tablosuna bakarak bütün işlerin çalışma geçmişini görmüş olmazsınız; zamanlama tanımı ile job çalışmasının sonucu farklı kayıtlardır.

Son yedekleme işlemlerini okumak için yetkili bir hesapla aşağıdaki sorguyu kullanabilirsiniz. Buradaki D, tam veritabanı yedeğini filtreler.

SELECT TOP (20)
    database_name,
    backup_start_date,
    backup_finish_date
FROM msdb.dbo.backupset
WHERE type = 'D'
ORDER BY backup_finish_date DESC;

Geçmişte bir yedeğin tamamlanmış görünmesi, dosyanın bugün hala bulunduğunu veya geri yüklenebildiğini kanıtlamaz. Dosya erişimini ve restore provasını ayrı kontrol edin. Geçmiş temizleme işi varsa çok eski kayıtları bu tabloda görememeniz de normal olabilir.

tempdb ne zaman disk kullanır?

tempdb, yerel ve global geçici tabloları, sıralama gibi işlemlerin ara sonuçlarını ve ilgili row versioning verilerini barındırabilir. SQL Server bellek önbelleğinden yararlanır ama veritabanının veri ve log dosyaları disk üzerindedir. Yoğun sorgularda bu alanın kapasitesi ve depolama gecikmesi önem kazanır.

Güncel tempdb belgesine göre SQL Server her başladığında tempdb yeniden oluşturulur. Geçici veriler önceki çalışma döneminden taşınmaz ve bu veritabanı için normal yedekleme/geri yükleme yapılmaz.

Ekrandaki geçici tablo fikrini küçük bir örnekle deneyebilirsiniz. Aşağıdaki kodu test bağlantısında çalıştırın, ardından başka bir sorgu penceresinde tabloyu okumayı deneyin.

CREATE TABLE #Kontrol (id int NOT NULL);
INSERT INTO #Kontrol VALUES (1);
SELECT id FROM #Kontrol;
DROP TABLE #Kontrol;

İkinci pencerede aynı login ile bağlanmanız yerel geçici tabloyu görünür yapmaz; bağlantı oturumu farklıdır. Örneği incelerken ilk pencerede DROP TABLE satırını en son çalıştırın. Stored procedure içinde oluşturulan geçici tabloların yaşam süresi de oluşturuldukları kapsamdan etkilenir.

Diskteki dosyaları ve tanımlı büyümeyi görmek için:

USE tempdb;
SELECT name, type_desc, physical_name,
       size * 8.0 / 1024 AS size_mb,
       growth, is_percent_growth
FROM sys.database_files;

Bu sorgu dosya boyutunu gösterir, bütün alanın dolu olduğunu söylemez. Boş alan ve oturumların tüketimini ayrıca incelemek gerekir. Yalnız dosyayı küçültmek de yükün nedenini çözmez; aktif sorgular aynı alanı tekrar isteyebilir.

Sunucuda bırakacağınız kontrol listesi

• master, model ve msdb için yedek ve geri yükleme planını kontrol edin.
• Uygulamaya özel nesneleri model üzerinden dağıtmayın.
• tempdb veri dosyalarının alanını ve büyüme ayarlarını izleyin.
• Job geçmişini, yedek dosyasını ve restore sonucunu ayrı doğrulayın.

Bir sistem veritabanının görevini bildiğinizde, sorun anında hangi kayda veya dosyaya bakacağınız da belirginleşir. Sorularınız olursa yorumlarda buluşalım.