Sql Server Tempdb Veritabanının Disk Yolunu Değiştirmek

Bu makalede sql server system veritabanlarından olan Tempdb veritabanının disk yolunu değiştirmiş olacağız. Tempdb veritabanı sql server servisimiz restart olduğunda tekrar sql server kurulum konfigürasyonumuzda belirlediğimiz disk yolunun altında oluşmaktadır.  Bunun için bir önceki makalemizde C diski altında C1 klasörünün altına oluşturmuş olduğumuz tempdb data filelarını yine C diski altında C2 klasörüne taşımış olacağız. Şunu da belirtmek gerekir ki tempdb system dosyaları ayrı bir disk altında başka bir system dosyalarının olmadığı bir disk ortamında tek başına konumlanması gerekmektedir. Data ve log dosyasının birlikte bulunmasında herhangi bir sakınca yoktur.

Tempdb disk yolunu bulamazsa sql server başlangıçta servis açılırken çalışmaz. Bu gibi durumlarda startup parametreleri ile yeni disk yolları aşağıdaki yapacağımız gibi set edilmektedir.

Tempdb veritabanımızın nerede konumlandığı aşağıdaki resimde görülmektedir.

Aşağıdaki komut yardımıyla tempdb veritabanımızın konumu ve kaç tane data file’dan oluştuğunu görebiliriz.

Aşağıdaki script ile tempdb dosyalarının boyutu, bulunduğu path bilgisi ve growth değerini görebiliriz.

USE tempdb;
GO
SELECT 
name AS [FileName],
size * 8 / 1024 AS [CurrentSizeMB], -- Şu anki boyut
growth * 8 / 1024 AS [GrowthIncrementMB], -- Artış miktarı
max_size * 8 / 1024 AS [MaxSize_MB], -- Maksimum boyut
physical_name AS [FilePath]
FROM sys.database_files;

İkinci bir komut:

select*from sys.sysaltfiles where dbid=DB_ID('TEMPDB')

Aşağıdaki script yardımıyla sql server servis’i bir daha açıldığında tempdb file’larını şu disklerde oluştur anlamına gelen script’i çalıştırabilirsiniz.   Name kısmı yukarıdaki sorgu sonucunuz gibi yazmalısınız.

use master
go
alter database tempdb modify file (name = tempdev, filename = 'C:\C2\Tempdb\tempdb.mdf')
go
alter database tempdb modify file (name = temp2, filename = 'C:\C2\Tempdb\tempdb_mssql_2.ndf')
go
alter database tempdb modify file (name = temp3, filename = 'C:\C2\Tempdb\tempdb_mssql_3.ndf')
go
alter database tempdb modify file (name = temp4, filename = 'C:\C2\Tempdb\tempdb_mssql_4.ndf')
go
alter database tempdb modify file (name = temp5, filename = 'C:\C2\Tempdb\tempdb_mssql_5.ndf')
go
alter database tempdb modify file (name = temp6, filename = 'C:\C2\Tempdb\tempdb_mssql_6.ndf')
go
alter database tempdb modify file (name = temp7, filename = 'C:\C2\Tempdb\tempdb_mssql_7.ndf')
go
alter database tempdb modify file (name = templog, filename = 'C:\C2\Tempdb\templog.ldf')
Go

Sorgumuzu başarılı bir şekilde çalıştırmış olduk.

Sql server servisi Restart edilene kadar tempdb veritabanımız eski belirlenmiş olan yol üzerinde konumlanmış olur. Şimdi Sql Server servisimizi Restart yapıyoruz. Servis’i Restart etmeden önce klasörümüzün boş olduğunu görmüş oluyoruz.

Restart sonucunda tempdb file’larımızın yeni dosya yolunda oluştuğunu görmüş oluyoruz.

Farklı bir komut sayesinde yeni tempdb yollarını öğrenmiş oluyoruz.

USE TempDB
GO
EXEC sp_helpfile
GO

Aşağıdaki komut ile hangi dosyada IO sorunu olduğunu görebiliriz.

SELECT 
    DB_NAME(mf.database_id) AS DbName,
    mf.physical_name,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS AvgWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf 
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id where DB_NAME(mf.database_id)='tempdb'
ORDER BY AvgWriteMs DESC;

Gerçek sistem üzerinde karşılaşılan bir sorun üzerine system restart olduktan sonra sql server tempdb dosyaları eşit bir şekilde büyümesi gerekirken bazı dosyaların anormal bir şekilde büyüdüğü görülmektedir. Aşağıdaki komut ile tempdb anlık boyutunu görebiliriz.

SELECT
name
,size*8.0/1024/1024 'Current Size in GB'
FROM
tempdb.sys.database_files

Yukarıdaki komutla anlık boyutlarına baktıktan sonra aşağıdaki komut yardımıyla tempdb dosyalarının initial size değerini görebiliriz.

SELECT
name
, size*8.0/1024/1024
'Initial Size in GB'
FROM master.sys.sysaltfiles WHERE dbid = 2

Resimde dikkat edilirse initial size değeri 120 GB yakın bir şey verilmiş. Buda sql server açılırken tempdb nin 120 GB yakın alan tahsis edeceğini performans anlamında bizlere sıkıntı yaşatacağı görülmektedir.

Böyle bir sorunla karşılaşılırsa modify komutuyla tüm dosyalar güncellenmelidir. Bu gibi initial size boyut değişikliğinde servis restart gerektirmez. Bu işlem ssms arayüzünden de yapılabilir.

Güncelleme komutu:

ALTER DATABASE tempdb MODIFY FILE( NAME = temp2, FILENAME = 'T:\TEMPDB\tempdb_mssql_2.ndf',SIZE = 1024,FILEGROWTH = 512);

Bu makalede Tempdb veritabanımızda data file’ların değişim işlemini ele almış olduk. Başka bir makalede görüşmek dileğiyle.

*Göklerde ve yerde ne varsa hepsi Allah’ındır. Allah’ın ilmi ve kudreti her şeyi kuşatmıştır. Nisa-126*