MSSQL Server “Operating system error 665.The requested operation could not be completed due to a file system limitation.” Hatasının Çözümü

Bu makalede DBCC CHECKDB komutunu çalıştırdığımızda veritabanımız üzerinde aşağıdaki hata mesajıyla karşılaşmış olduk.

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000c455812c000 in file ‘F:\VERI\XXX.mdf_MSSQL_DBCC47’.

SQL Server, CHECKDB işlemi sırasında verinin o anki halinin bir “snapshot”ını alır. Bu snapshot, disk üzerinde “sparse file” olarak tutulur. 15 TB’lık bir veritabanında, uzun süren bir işlem boyunca çok fazla yazma/değişiklik oluyorsa, bu snapshot dosyası aşırı derecede parçalanır. NTFS dosya sistemi, bir dosya için tutulabilen “attribute list” (özellik listesi) sınırına ulaştığında bu işlemi durdurur ve 665 hatasını fırlatır.

İlgili Job’ın Output dosyası incelendiğinde ilgili Job’ın kill edildiği görülmektedir. Neden kill edildiğine dair herhangi bir hata mesajı yok.

Sql server Error loglarından veya Event Viewer baktığımızda aynı hata mesajının tekrarlandığını hata mesajından önce herhangi bir herhangi bir error veya information görülmedi.

Bu hatanın incelenmesi sonucunda bazı analizler yapılmıştır. Bu hataya nelerin sebep vereceğini nelerin yapılması gerektiğini ele alalım.

Öncelikle DBCC Checkdb hangi data file üzerinde işlem yapacaksa ilgili data file’ın olduğu diskte F:\VERI\XXX.mdf_MSSQL_DBCC47 uzantısında Internal database snapshot yapısı oluşturmaktadır. Yoğun şekilde kullanılan datafile’lar üzerinde oluşturulması snapshot’ın büyümesine sebep verecektir. Bu sebepten data file’ın bulunduğu disk’te %20-30 oranında boş alan olması gerekmektedir.

Şimdi ilgili hatanın alınmaması için diğer önerilen yöntemlere değinelim:

• Disklerimizin nvme veya ssd diskler olması gerekmektedir.
• Diskteki %20-30 oranında boş alan olması gerekmektedir.
• Antivirus/backup yazılımları dosya erişimini engelliyor olabilir
• Büyük bir veritabanını daha küçük dosyalara ayırın. Örneğin, bir 8 TB’lık veri dosyanız varsa, onu sekiz 1 TB’lık veri dosyasına bölebilirsiniz. Bu seçenek yardımcı olabilir çünkü daha küçük dosyalarda daha az değişiklik olur. Ayrıca, veri taşınırken dosyalar kompakt şekilde düzenlenir ve fragmentation azalır. Daha sonra mevcut indexlerimizi tekrardan Rebuild ederek indexlerimizin yeni oluşturulacak data file üzerinde büyümesi sağlanacak.
• Ntfs dosys sistemden Refs dosya sistemine geçilmesi gerekmektedir. Bu işlem için diskin formatlanması gerekmektedir. Bunun için veritabanının refs şeklinde formatlanması gerekmektedir.
• Veritabanının auto growth değeri ve diskimizin Allocation unit size değerinin sql server fragmentation’lara sebep vermemek için 64 KB veya database data file auto growth değerinin 512 MB olarak ayarlanması gerekmektedir.
• Yedek alınırken MAXTRANSFERSIZE BLOCKSIZE parametrelerinin dikkatli seçilmesi gerekmektedir. @BlockSize SQL Server’ın yedek alırken diske kaç baytlık bloklar halinde yazacağını belirler. Amaç disk I/O verimliliğini artırmak ve gereksiz disk fragmentation’ı azaltmak. @MaxTransferSize SQL Server’ın backup sırasında tek seferde belleğe alıp diske yazacağı maksimum veri miktarını belirler.

EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'XXXXX', 
@Directory = N'M:\YEDEK23\BACKUP', 
@BackupType = 'FULL', 
@CleanupTime = 144, 
@CleanupMode = 'BEFORE_BACKUP', 
@Verify = 'Y',  
@Checksum = 'Y', 
@LogToTable = 'Y', 
@Compress= 'Y', 
@BlockSize= 4096,--4 KB 
@NumberOfFiles = 8, 
@BufferCount=50, 
@MaxTransferSize = 4194304

• Dbcc checkdb işlemlerinde 665 hatası almamak için PHYSICAL_ONLY parametresiyle çalıştırılması gerekmektedir. Bu işlem veritabanında mantıksal kontrol işlemleri yapmaz.

• Allocation Unit: 64K olmalıdır.(Bytes Per Cluster) Ayrıca Bytes Per FileRecord Segment değerinin 4096 olması disk fragmentation önüne geçilmiş olur.

NOT: Bytes Per FileRecord Segment değerini 4096 değerine çekmemiz Bytes Per Cluster değerini default değere çekmektedir. Dikkat edilmesi gerekmektedir.

fsutil fsinfo ntfsinfo F:\

• NTFS Fragmentation aşağıdaki komutla kontrol edilmesi gerekmektedir. Fragment sayısı çok yüksekse, Free space fragmented görünüyorsa bu hatanın ana sebebi budur.

defrag F: /A /V

Sparse file desteği aşağıdaki komutla kontrol edilebilir.

fsutil sparse queryflag F:\VERI\XXX.mdf

• Dbcc checkdb işlemlerinde 665 hatası alınmasından dolayı ilgili hata incelendiğinde rebuild işlemi yapılmasına karar verilmiştir. Veritabanı üzerinde bulunan büyük tablolarda fragmentation çok yüksek orandadır. Aynı boyuttaki farklı veritabanlarında sorun alınmayıp ilgili veritabanından sorun alınması sorunun index fragmentation ve data file küçültme işlemiyle çözüleceği kanaatine varılmıştır.

Bu sebepten ilgili veritabanının mevcut olan data file lar kapatılır. Auto Growth değeri none’a çekilir. Data file’lar yeni disk seçilerek snapshotların mevcut disk üzerinde büyümesi gerekmektedir. Yeni oluşturulacak olan data file üzerinden devam etmesi için bu işlem yapılmaktadır. Bu yapının oluşturulmasında ki bir başka sebep dbcc checkdb işlemi çalışırken aynı disk altında internal snapshot oluşturulur. Bu işlem sırasında değişen veriler snapshot üzerine yazıldığı için boyutun zamanla büyümesi bir başka sebep ise mevcut veritabanının bulunduğu disklerin fragmentation olmasından dolayıdır. Sadece tablo bazlı fragmentation gerçekleşmez. Disk bazlıda fragmentation gerçekleşmektedir.

Yukarıdaki işlemlerden sonra İlgili veritabanı üzerinde report diyerek hangi tablo üzerinden rebuild işlemini yapacağımızı belirliyoruz. Tablolarımızı çok büyük olduğu ve rebuild işlemi yapılacağı için rebuild işlemini tablo bazlı yapılmaktadır. Sebebi ise log tablomuzun aşırı derecede büyümesinden dolaydır. Bu işlemler sırasında ldf dosyamızın kontrol edilmesi aynı zamanda log backupların alınıp alınmadığını çünkü ldf dosyamızın boyutu aşırı derecede büyür. Bunun yanında Alwayson yapısının aynı anda kontrol edilmesi gerekmektedir.

İlgili veritabanında bulunan tablolarda fragmentation oranlarını bulmak için fragmentation komutu kullanılmaktadır.

use DB_NAME
go
SELECT S.name as 'Schema',
T.name as 'Table',
indexstats.avg_fragmentation_in_percent,
ind.type_desc,
ind.name as Index_Nmae,
indexstats.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
INNER JOIN sysobjects so ON so.id=ind.object_id
INNER JOIN sysusers su on su.uid=so.uid 
INNER JOIN sys.tables T on T.object_id = indexstats.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id

WHERE indexstats.avg_fragmentation_in_percent > 5
  AND ind.name is not null --HEAP'lerde index olmuyor!...
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Rebuild edilecek tablomuzu belirledikten sonra tablomuzun altında bulunan Clustered ve non clustered indexler yeni olan data file üzerinden büyümüş olacaktır. Bu şekilde mevcut büyük olan data file küçültülerek dataların tüm data file’lara eşit bir şekilde yayılması sağlanmış olacağız. Rebuild işlemlerinden sonra ilerleyen aşamalarda data file’lar arasında empty file shrink yöntemi ile data file’ların boyutunu düşürmüş olacağız.

Yapılan sıkıntı yaratan bölüm ben tablolarımda index rebuild yapıldığında mevcut mdf altında boşalan alanın bir sonraki rebuild işlemi ile doldurmaya çalıştığından dolayı data file’ın shrink edilip daha sonra rebuild işleminin yapılması veya tüm tabloların aynı anda rebuild edilerek eski olan mdf dosyasına yazmasının önüne geçmiş oluruz.

Diskimizde alan olsa bile farklı disk altında data file ekleme işlemi yapmamız. Disk fragmentation olmayan bir diskte yapmamız performans ve sorunun giderilmesi açısından verimli olacaktır.

• Allocation Unit: 64K olmalıdır.(Bytes Per Cluster) Ayrıca Bytes Per FileRecord Segment değerinin 4096 olması disk fragmentation önüne geçilmiş olur.

fsutil fsinfo ntfsinfo F:\

• NTFS Fragmentation aşağıdaki komutla kontrol edilmesi gerekmektedir. Fragment sayısı çok yüksekse, Free space fragmented görünüyorsa bu hatanın ana sebebi budur.

defrag F: /A /V

Sparse file desteği aşağıdaki komutla kontrol edilebilir.

fsutil sparse queryflag F:\VERI\XXX.mdf

Not: DBCC CHECKDB işlemini snapshot olmadan çalıştırılması için TABLOCK parametresinin eklenmesi gerekmektedir.

Bu makalede The operating system returned error 665 The requested operation could not be completed due to a file system limitation hatasına nelerin sebep olduğunu görmüş olduk.

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

“Sizin en hayırlılarınız, hanımlarına karşı en iyi davrananlarınızdır. (Hadis)