Microsoft SQL Server’da Auto Growth (Otomatik Büyüme) Yapılandırması

Merhabalar bu makalede, Microsoft SQL Server üzerindeki bir veritabanı için auto growth (otomatik büyüme) seçeneklerini ve SQL Server üzerinde veritabanı büyümenizi uygun şekilde yönetmek için onu nasıl kullanmanız gerektiğini açıklayacağım. Ek olarak veritabanınız için auto growth olaylarınızı yönetmenize yardımcı olacak bazı komut dosyalarını da paylaşacağım.

Auto Growth (Otomatik Büyüme) Nedir?

Auto Growth olayı, SQL Server Database Engine’in bir veritabanı dosyasının alanı tükendiğinde boyutunun genişletme işlemidir. Bir veritabanı dosyasının büyüme miktarı, veritabanınız için dosya büyütme seçenekleri için sahip olduğunuz ayarlara bağlıdır. SQL Server üzerinde her veritabanı dosyasının bir auto growth ayarı vardır. Veritabanı dosyalarınızın nasıl büyüyeceğini belirlemek için kullanabileceğiniz üç farklı ayar vardır;

  • Belirli bir boyuta,
  • Mevcut boyutun bir yüzdesine göre büyüyebilirler
  • Hiç büyümeyebilirler.

Ek olarak dosyalarınızı sınırsız büyümeye ayarlayabilirsiniz. Bu durum daha fazla alana ihtiyaç duydukça veya disk alanınız tükendikçe büyümeye devam edecekleri anlamına gelir. Bir veritabanı dosyasının büyümesini, belirtilen boyuttan daha büyük olmayacak şekilde sınırlayabilirsiniz. Bu farklı auto growth ayarlarının her birinin varsayılanları vardır veya bunları her veritabanı dosyası için ayarlayabilirsiniz.

Bir veritabanı için Auto Growth ayarınızı düzgün bir şekilde yönetmiyorsanız, veritabanınız çok sayıda veya çok az sayıda Auto Growth olayı yaşayabilir. Auto Growth olayı gerçekleştirildiğinde SQL Server, bir Auto Growth olayı meydana gelirken veritabanı veri işlemeyi durdurur. Bu Auto Growth olayı tamamlanırken bu veritabanına karşı işlemenin durdurulacağı anlamına gelir. Sürekli büyüyen veritabanına karşı işlenmekte olan SQL komutları için daha yavaş yanıt süresi anlamına gelir.

SQL Server, mevcut işlemlerde disk alanı sorunlarından kaçınmak için tek bir dosyanın auto growth ayarına dayalı olarak bir veritabanının (veri ve log dosyası) boyutunu genişletir. Auto growth olayı gerçekleştiğinde SQL Server veri işlemeyi durdurur. Database performansı açısından için auto growth ayarını kontrol etmelisiniz ve bunu gözlemlemeniz gerekiyor. Auto Growth işlemi uzun sürerse veritabanı performansını ve kullanılabilirliğini etkilemeketedir.

Bir Auto Growth olayı meydana geldiğinde, SQL Server’ın veritabanının büyüyebileceği ek disk alanı bulması gerekir. Büyük olasılıkla bu disk alanı fiziksel olarak mevcut veritabanı alanının yanında olmayacak bunun yerine diskte başka bir yerde olacaktır. Bu durumda veritabanı dosyanızın diskte fiziksel olarak parçalanmasına neden olur. Ne kadar çok Auto Growth olayına geçekleşirse o kadar fazla fiziksel parçalanma yaşarsınız. Veritabanınız fiziksel olarak parçalandığında SQL Server’ın bu veritabanlarını okuması daha uzun sürer çünkü veritabanınızı okumak için disk kafasını tüm farklı parçalanmış parçalara taşıması gerekir. Auto Growth olaylarıyla ilgili sorunlardan kaçınmak için meydana gelen Auto Growth olaylarının sayısını en aza indirmeniz gerekir.

Bir veritabanı için varsayılan Auto Growth ayarları veritabanınızın nasıl büyümesi gerektiği konusunda ideal ayarlardır. Veritabanınızı ilk oluşturduğunuzda büyüme profili hakkında bir fikriniz varsa Auto Growth özelliklerinizi bu büyüme projeksiyonlarına göre ayarlamanız gerekir. Veritabanınızın ne kadar hızlı büyüyeceği hakkında hiçbir fikriniz yoksa, Auto Growth olaylarını izliyor olmalısınız. Veritabanınızın ne sıklıkla büyüdüğünü bilmek, veritabanınızın büyüme hızı hakkında size bazı fikirler verecektir.

Varsayılan Auto Growth Değeri

Varsayılan olarak, Auto Growth da dosya büyümesi 64 MB ve maksimum dosya boyutu limitsiz olarak tanımlı gelmektedir. Daha önceki SQL Server sürümlerinde ise bu değerler 1 MB olup SQL Server veritabanındaki mevcut dosya boyutunun %10’unu büyütecek şekilde ayarlanmıştır. Küçük veritabanları için uygun olabilir, ancak büyük bir veri tabanı dosyası perspektifinden bakarsanız ideal bir yapılandırma değildir.

Yeni Bir Veritabanı Oluştururken Auto Growth Ayarının Yapılması

Veritabanı Auto Growth ayarını SQL Server Management Studio, komut dosyasıyla oluşturulmuş SMO kullanarak veya veritabanınızı oluştururken T-SQL kullanarak yapabilirsiniz. Bu yöntemleri mevcut veritabanlarının Auto Growth ayarlarını değiştirmek için de kullanabilirsiniz.

Bu bölümde bir veritabanı oluşturduğunuzda SQL Server Management Studio’yu kullanarak Auto Growth ayarlarını nasıl yapacağını gösteceğim.

Auto Growth için başlangıç ayarları, yeni bir veritabanı oluşturulurken; varsayılan değerlere göre ayarlanır. Bu varsayılanlar model veritabanı dosyalarındaki Auto Growth ayarları kullanılarak belirlenir. “VKK-DB01” adında yeni bir veritabanı oluştururken “New Database” ekran görüntüsünde varsayılan değerlerimi görebilirsiniz.

Aşağıdaki resimde kırmızı daire içine alınmış olan üç nokta butonlarına tıklayarak oluşturulacak olan veritabanı için Auto Growth ayarlarını görebilirsiniz.

Bu ekrandaki 3 nokta’lı butona tıkladığımda Aşağıdaki pencere açılacaktır. Böylece oluşturulacak olan veritabanının veri dosyası için varsayılan Auto Growth ayarı sınırsız büyüme’ye sahip ve 64 MB oranında büyüyecektir.

Log dosyası, sınırsız büyüme ile 64 MB oranında büyüyecek şekilde ayarlanmıştır. Model veritabanı ayarlarınızı değiştirmediyseniz eğer benim yaptığımla aynı varsayılan Auto Growth ayarına sahip olacaksınız.

Yukarıdaki resimde gösterilen iletişim kutusunu kullanarak dosya auto growth’da üç farklı olası değerden herhangi biri ile değiştirebilirsiniz;

  • İlk seçenek en üstteki “Enable Autogrowth” yazan onay kutusudur. Bu seçenek, “VKK-DB01”de Auto growth ile database’in büyüyüp/büyümeyeceğini açmanıza veya kapatmanıza olanak tanımaktadır. Bu kutuyu işaretleyerek SQL Server’a bu veritabanının otomatik olarak büyümesine izin vermesini söylüyorum. Bu kutunun işaretini kaldırarak SQL Server’a veritabanımın otomatik olarak büyümesini istemediğimi söylüyorum.
  • Ayarlayabileceğim bir sonraki seçenek “File Growth” seçeneğidir. Veritabanın bulunduğu disk üzerindeki boş alan bittiğinde veri dosyamın nasıl büyüyeceğinin ayarlanmasına izin vermektedir. Gördüğünüz gibi, dosya boyutunun % (yüzde) veya MB olarak büyümesini isteyip/istemediğinizi belirleyen iki adet radyo düğmesi vardır.

Normalde veritabanı otomatik büyütme ayarlarımın % (yüzde) yerine MB bazında büyümesine izin verilmesini tavsiye ederim. Bunu size öneriyorum çünkü; tüm auto growth olayları tek tip sabit boyutta olmasını istiyorum. Yüzde bazında büyümelerine izin verecek olsaydım eğer; veri tabanım büyüdükçe otomatik büyüme miktarı da buna bağlı olarak artacaktır.

Veritabanlarının yüzde olarak büyümesine izin verildiğinde; büyüdükleri miktar, özellikle büyük multi-gigabayt veya terabayt veritabanları için önemli olabilir. Büyük bir veri tabanı bir yüzdeye dayalı olarak büyüyecekse, büyük olasılıkla çok daha fazla yer kaplayacaktır bu gibi durumlarda önümüzdeki yıl için büyümeyi idare etmesi gerekecektir. Bu durum büyük veritabanlarının disk alanını daha fazla alan tahsis edilmesine neden olur. Daha da kötüsü büyük bir veritabanı o kadar büyüyebilir ki tüm kullanılabilir disk alanını kaplar ve bunun olmasını bir çok sistem yöneticisi de istemez. Yeni bir veritabanının büyümesi gereken megabayt sayısını ayarlarken bunu yeni veritabanının büyüme profiliyle ilgili sahip olduğum bilgilere dayanarak yapmalısınız.

  • Son seçenek “Maximum File Size” seçeneğidir. Bu seçenek, SQL Server’ın dosya büyümesine izin vereceği maksimum boyutu ayarlamama izin veriyor. Normalde sınırsız büyüme seçeneğini kullanırım ama uygulamalarımın çoğunun iyi niyetli olduğunu ve kontrolsüz bir şekilde büyüme ihtimalinin çok düşük olduğunu biliyorum. Ayrıca sınırsız seçeneklerin kullanılması auto growth kısıtlamalarından kaynaklanan uygulama hatalarını en aza indirir. Bu seçenek ile disk alanı kullanımını ve otomatik büyüme olaylarını oldukça yakıdan izlemenizde gerekecektir. Tek bir veritabanının tüm disk alanınızı kullanmadığından emin olmak istiyorsanız, maksimum dosya boyutunu ayarlamak bunu önlemenin bir yolu olacaktır.

SQL Server’da yeni bir veritabanı oluşturmak için T-SQL kullanıyorsanız, CREATE DATABASE komutunda auto growth seçeneklerini ayarlayabilirsiniz. Aşağıdaki tabloda “VKKDB02” adında yeni bir veritabanı oluşturduğumda auto growth seçeneğini nasıl ayarlayabileceğinin örneği yer almaktadır.

USE MASTER;
GO
CREATE DATABASE “VKKDB02”
ON
( NAME = VKKDB02_data,
    FILENAME = ‘S:\SQL-DB\VKKDB02.mdf’,
    SIZE = 1024MB,
    MAXSIZE = 4096MB,
    FILEGROWTH = 128MB )
LOG ON
( NAME = VKKDB02_log,
    FILENAME = ‘S:\SQL-LOG\VKKDB02.ldf’,
    SIZE = 256MB,
    FILEGROWTH = 128MB ) ;
GO

Yukarıdaki kod incelendiğinde veritabanı dosyalarının auto growth seçeneklerini kontrol etmek için CREATE DATABASE komutunun MAXSIZE ve FILEGROWTH seçeneklerini kullandığınıı görebilirsiniz.

Buradaki MAXSIZE seçeneği bir dosyanın ne kadar büyüyebileceğini belirtmek için kullanılır ve FILEGROWTH seçeneği dosyanın her büyümesi gerektiğinde ne kadar büyümesi gerektiğini belirtmek için kullanılır.

Bu örnekte yer alan “VKKDB02_data” dosyamın 125 MB‘lik parçalar halinde maksimum 4096 MB boyutuna kadar büyümesine izin veriyor. “VKKDB02_log” log dosyası için, “MAXSIZE” seçeneği sağlamadığım için sınırsız büyümesine izin verildi ancak büyüdüğünde “128 MB” parçalar halinde büyüyecektir.

Varsayılan Auto Growth Ayarlarını Kullanan Veritabanlarının Tespit Edilmesi

SQL Server üzerinde gelen model sistem veri tabanıyla ilişkili olarak varsayılan auto growth ayarları veritabanlarının nasıl büyüdüğüne ilişkin en iyi ayarlar değildir. Bir SQL Server Instance devraldıysanız veya veritabanlarını oluştururken auto growth parametrelerini ayarlama konusunda titiz davranmadıysanız hangi veritabanlarının varsayılan ayarı kullandığını belirlemek için Instance üzerindeki veritabanlarını taramak isteyebilirsiniz.

SQL Server verileriyle yüklenen model veritabanı için otomatik büyütme için varsayılan ayarların veri dosyaları ve log dosyaları için güncel SQL Server sürümlerinde 64 MB olduğunu unutmayınız. Bu nedenle, varsayılan auto growth ayarlarını kullanan veritabanlarını belirlemek için bir T-SQL betiği çalıştırmak basit ve pratik bir çözüm olacaktır.

Aşağıda varsayılan auto growth ayarlarını kullanan veritabanlarını tanımlamak için kullandığım komut dosyası yer almaktadır.

— Drop temporary table if it exists
IF OBJECT_ID(‘tempdb..#info’) IS NOT NULL
       DROP TABLE #info;
 
— Create table to house database file information
CREATE TABLE #info (
     databasename VARCHAR(128)
     ,name VARCHAR(128)
    ,fileid INT
    ,filename VARCHAR(1000)
    ,filegroup VARCHAR(128)
    ,size VARCHAR(25)
    ,maxsize VARCHAR(25)
    ,growth VARCHAR(25)
    ,usage VARCHAR(25));
   
— Get database file information for each database  
SET NOCOUNT ON;
INSERT INTO #info
EXEC sp_MSforeachdb ‘use ?
select ”?”,name,  fileid, filename,
filegroup = filegroup_name(groupid),
”size” = convert(nvarchar(15), convert (bigint, size) * 8) + N” KB”,
”maxsize” = (case maxsize when -1 then N”Unlimited”
else
convert(nvarchar(15), convert (bigint, maxsize) * 8) + N” KB” end),
”growth” = (case status & 0x100000 when 0x100000 then
convert(nvarchar(15), growth) + N”%”
else
convert(nvarchar(15), convert (bigint, growth) * 8) + N” KB” end),
”usage” = (case status & 0x40 when 0x40 then ”log only” else ”data only” end)
from sysfiles
‘;
 
— Identify database files that use default auto-grow properties
SELECT databasename AS [Database Name]
      ,name AS [Logical Name]
      ,filename AS [Physical File Name]
      ,growth AS [Auto-grow Setting] FROM #info
WHERE (usage = ‘data only’ AND growth = ’64 MB’)
   OR (usage = ‘log only’ AND growth = ’64 MB’)
ORDER BY databasename
 
— get rid of temp table
DROP TABLE #info;

Mevcut Bir Veritbanının Auto Growth Ayarlarını Değiştirme

Mevcut bir veritabanı üzerinde Auto Growth ayarlarını değiştirmek için; SQL Server Management Studio yada T-SQL’de sorgu kullanarak değiştirebilirsiniz. SQL Server Management Studio’da Auto Growth ayarını değiştirmek için istediğiniz veritabanında sağ tıklayın gelen menüden “Properties” öğesine tıklayınız.

Seçtiğiniz veritabanı için “Database Properties” iletişim kutusunu getirecektir. Ardından, “Files” bölümünü seçiniz. Bu bölümde veritabanı dosya bilgilerini görüntüleyecektir. Auto Growth ayarını değiştirebileceğiniz pencereye gitmek için, 3 noktayı (“…”) içeren veya genellikle elips düğmesi olarak adlandırılan düğmeyi tıklayınız.

Aşağıdaki resimde görüldüğü gibi “Change Autogrowh for … ” penceresini açacaktır. “Change Autogrowth for …” penceresinde, SQL Server Management kullanarak bir veritabanı oluştururken yaptığınız gibi, veritabanınız için Auto Growth ayarlarını değiştirebilirsiniz.

Aşağıdaki t-sql komutu ile “AdventureWork2019” adlı veritabanı için FILEGROWTH ayarlarını değiştiren bir SQL ifadesi yer almaktadır.

ALTER DATABASE AdventureWork2019
  MODIFY FILE
  (NAME=AdventureWork2019_Log,FILEGROWTH=128MB);

ALTER DATABASE deyiminde, AdventureWork2019 veritabanındaki “AdventureWork2019_Log” adlı işlem günlüğü dosyasının FILEGROWTH boyutunu 128 MB olarak değiştirilebilir.

SQL Server üzerinde bütün veritabanlarında bu değişkliği yapmak için aşağıdaki sorguyu çalıştırabilirsiniz.

SELECT ‘ALTER DATABASE [‘ + db_name(s.database_id) + ‘]
      MODIFY FILE ( NAME = N”’ + s.name + ”’, FILEGROWTH = 128 MB)’ as ToExecute
FROM sys.master_files s
INNER JOIN sys.databases db ON s.database_id = db.database_id
where (s.is_percent_growth = 1
or s.growth * 8.0 / 1024 < 10)
and db.state_desc = ‘online’
ORDER BY s.database_id

Bir Otomatik Büyüme Olayının Ne Sıklıkta Gerçekleştiğini Belirleme

SQL Server bir otomatik büyüme olayı gerçekleştirdiğinde, otomatik büyüme olayını tetikleyen işlemin, işlem bitmeden önce otomatik büyüme olayının tamamlanmasını beklemesi gerekir. Bu otomatik büyüme olayları, bir otomatik büyüme olayı gerçekleştiğinde performansınızın biraz düşmesine neden olur. Bu nedenle, otomatik büyüme olaylarının nadiren gerçekleşmesi için veritabanınızı uygun şekilde boyutlandırmanız en iyisidir.

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);
DECLARE @DL VARCHAR(1000); — email distribution list
DECLARE @ReportHTML  NVARCHAR(MAX);
DECLARE @Subject NVARCHAR (250);
 
— Set email distrubution list value
SET @DL = ‘kadir@kadirkozan.com’
 
— Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;
 
— rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX(‘.’,@filename);
SET @ec = CHARINDEX(‘_’,@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));
 
— set filename without rollover number
SET @filename = @bfn + @efn
 
— Any Events Occur in the last day
IF EXISTS (SELECT *
             FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
               WHERE (EventClass = 92  — Date File Auto-grow
                   OR EventClass = 93) — Log File Auto-grow
                  AND StartTime > DATEADD(dy,-1,GETDATE()))
BEGIN — If there are autogrows in the last day
  SET @ReportHTML =
    N'<H1>’ + N’Auto-grow Events for ‘ +
   CAST(SERVERPROPERTY(‘MachineName’) AS NVARCHAR(128)) +
    + CASE WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
           THEN ” 
           ELSE N’\’ +  CAST(SERVERPROPERTY(‘InstanceName’) AS NVARCHAR(128))
      END +
    N'</H1>’ +
    N'<table border=”1″>’ +
    N'<tr><th>Start Time</th><th>Event Name</th>’ +
    N'<th>Database Name</th><th>File Name</th><th>Growth in MB</th>’ +
    N'<th>Duration in MS</th></tr>’ +
    CAST((SELECT
              td = ftg.StartTime, ”,
              td = te.name, ”,
              td = DB_NAME(ftg.databaseid), ”,
              td = Filename, ”,
              td =(ftg.IntegerData*8)/1024.0, ”,
              td = (ftg.duration/1000)
          FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
               INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id 
          WHERE (EventClass = 92  — Date File Auto-grow
              OR EventClass = 93) — Log File Auto-grow
             AND StartTime > DATEADD(dy,-1,GETDATE()) — Less than 1 day ago
          ORDER BY StartTime 
          FOR XML PATH(‘tr’), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>’ ;
   
        — Build the subject line with server and instance name
    SET @Subject = ‘Auto-grow Events in Last Day ‘ +
                   CAST(SERVERPROPERTY(‘MachineName’) AS NVARCHAR(128)) +
                 + CASE WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
                        THEN ” 
                        ELSE N’\’ +  CAST(SERVERPROPERTY(‘InstanceName’) AS NVARCHAR(128))
                   END
 
    — Send email to distribution list.    
    EXEC msdb.dbo.sp_send_dbmail @recipients=@DL,
           @subject = @Subject, 
           @body = @ReportHTML,
           @body_format = ‘HTML’,
           @profile_name=’Server_DBAs Mail’ ;
END; — If there are autogrows in the last day

Bu kod ile 24 saat içinde meydana gelen tüm auto growth olaylarını gösterir. Bir auto growth olayı gerçekleşmesi durumunda kod içerisindeki mail adresine auto growth olaylarının bir raporunu e-posta ile gönderir. Eğer veri tabanı için toplam auto growth olayının sayısını görmek isterseniz SQL Server raporlarını kullanabilirsiniz. Bu işlem rapor almak istediğiniz veri tabanı üzerinde yan tıklayınız açılan seçeneklerden sırasıyla; Reports -> Standard Reports -> Disk Usage seçilir.

Açılan rapor ekranında auto growth olayına ait bilgilere “Data/Log Files Autogrow/Autoshrink Events” bölümünden kontrol edebilirsiniz.