Bu makalede sql server index maintance çalışması yaparken command log tablosuna düşen hata üzerine makale yazma gereği duydum. Öncelikle ola hallengren scriptleri kullanmaktayım. Haftalık çalışan büyük bir veritabanında hata alınca command log tablosuna bakınca makale başlığında da görüldüğü gibi 1105 hatası alınmış oldu.
Aşağıdaki resimde de görülmektedir.

İlgili hata mesajı:
Could not allocate space for object ‘dbo.LOGMRNYGNMETOTLAR’.’IX_MENUKOD’ in database ‘DBLOG’ because the ‘SECONDARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Şimdi gelelim yukarıdaki hata mesajının neden kaynaklandığını ele alalım. Genel olarak hata mesajında belirtilen tabloda ilgili index üzerinde işlem yaparken belirtilen file group’un bağlı olduğu data file üzerinde alan olmadığı ve auto growth değerinin None’a çekildiğini söylemektedir. Bunun için ilgili filegroup’a bir data file eklememizi veya auto growth değerini none ifadesine çekmemiz gerektiğini söylemektedir.
İlgili olarak veritabanımızın data file’larına bakalım.

Yukarıda üzerinde işlem yapılan index üzerinde hata mesajı almamızın öncelikle yukarıda None değerine çekilen data file değerlerinden kaynaklandığı görülmektedir. Öncelikle index yapımız da rebuild işlemi gerçekleşseydi rebuild edilen index yeni aktif olan data file üzerinden oluşturulmuş olacaktı ve sorun teşkil etmeyecekti.
Hata alınan index yapımızın Reorganize işlemi olduğu görülmektedir.
ALTER INDEX [IX_MENUKOD] ON [dbo].[LOGYNSNMETOTLAR] REORGANIZE WITH (LOB_COMPACTION = ON)
Diyebilirsiniz Reorganize işleminde yeniden bir şey yapmıyor neden bu işlemle karşılaşıyorum. Öncelikle REORGANIZE işleminin çalışma mantığı REBUILD’den farklı olduğu için alan yetersizliği hatası vermesi ilk etapta şaşırtıcı gelebilir. Çünkü REORGANIZE yeni bir indeks yapısı oluşturmaz, mevcut sayfaları (pages) kendi içinde kaydırıp birleştirir.
Not: Data file genişletilemiyor ve yeni bir yazacağı alan yoksa REBUILD işlemini SORT_IN_TEMPDB = ON ile tempdb’ye yönlendirilebilir. REORGANIZE parametresinde SORT_IN_TEMPDB desteği yoktur
Not: REORGANIZE Her zaman tablonun/indeksin bulunduğu filegroup üzerinde çalışmak zorundadır, yönlendirilemez. Filegroup içinde boş alan yoksa ve tüm dosyaların AUTOGROWTH değeri NONE ise REORGANIZE işlemi kaçınılmaz olarak patlar.
Yukarıdaki scripttede görüldüğü gibi temel sebep LOB (Large Object) veri tipinden kaynaklanmaktadır. Tablonuzda VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT veya IMAGE gibi LOB (Large Object) kolonlar varsa ya da indeks içi doluluk oranlarından dolayı sayfa bölünmesi (page split) gerektiğinde, SQL Server REORGANIZE yaparken yeni veri sayfaları allocate (tahsis) etmek zorundadır.
REORGANIZE işlemi, LOB verilerini sıkıştırırken veya geçici sayfa kaydırmaları yaparken SECONDARY filegroup içerisinde anlık boş sayfalara ihtiyaç duyar. Bu sebepten ötürü ilgili hatayı fırlatmaktadır.
ALTER INDEX … REORGANIZE komutu varsayılan olarak LOB_COMPACTION = ON parametresiyle çalışır. Bu işlem, LOB veri türlerini fiziksel olarak yeniden düzenlerken geçici olarak filegroup içinde alan genişlemesine ihtiyaç duyabilir.
Tablonuzda LOB kolonlar varsa, reorganize işleminin LOB verilerini sıkıştırmasını engelleyerek sayfa allocate etme ihtiyacını ortadan kaldırabilirsiniz.
ALTER INDEX [IX_MENUKOD] ON [dbo].[LOGYNSNMETOTLAR] REORGANIZE WITH (LOB_COMPACTION = OFF)
Not: Ola hallengreen scriptleri kullanıyorsanız ilgili parametrenin script içerisinde No değerine çekilmesi gerekmektedir.
Not: Tablomuzda LOB veriler yoksa sadece standart kolonlar için bir Reorganize işlemi yapılıyorsa sql server arka planda Page split ve Forwarded Records işlemlerinden dolayı bu hata mesajını fırlatmış olabilir. İki sayfa birleştirilmeye çalışılırken veya veriler yer değiştirirken mevcut sayfaya sığmayan satırlar için yeni bir Data Page tahsis edilmek (allocate) istenebilir.
Not: Lock Escalation ve Allocation Bitmap işlemleri yüzünden hata mesajı fırlatmış olabilir. REORGANIZE mikro-işlemler (short transactions) halinde çalışır. Sayfaları taşırken IAM (Index Allocation Map) ve GAM/SGAM (Global Allocation Map) gibi veritabanının iç alan haritalarında değişiklik yapar. Eğer filegroup içerisindeki dosyalarda Unallocated Space (tahsis edilmemiş alan) sıfırlandıysa, SQL Server dahili harita güncellemeleri için bile yer bulamaz.
DBCC CHECKDB işlemi çalışırken aynı şekilde Error Number 1105 hatası alınabilir. Burada hata mesajına odaklanılarak neler yapılması gerektiğini ele alabilir.
Could not allocate space for object ‘dbo.SORT temporary run storage: 141321204400128’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Eğer amacınız standart bir rutin kontrol ise, WITH PHYSICAL_ONLY seçeneğini kullanmak tempdb kullanımını minimuma indirir ve işlemi çok daha hızlı tamamlamanızı sağlar. Bu parametre, yapısal bozulmaları kontrol ederken yoğun geçici tablolar oluşturmaktan kaçınır. Always On yapılarındaki ikincil sunucularda (secondary replica) bakım scriptleri çalıştırılırken tempdb performansı kritik önem taşır. Eğer tempdb tek bir dosyadan oluşuyorsa, işlemci çekirdek sayınıza uygun olacak şekilde (genellikle mantıksal işlemci sayısı kadar veya maksimum 8’e kadar) eşit boyutta birden fazla tempdb veri dosyası eklemek bu tarz şişme ve kilitlenme sorunlarını büyük ölçüde engeller. Tempdb başlangıç boyutlarının(Initial Size) ihtiyaç duyulan büyük boyuta manuel olarak çıkararak anlık büyüme maliyetlerinin önüne geçilmesi gerekmektedir.
Not: Datanın bulunduğu data file olan yapının auto growth değeri kapatılmışsa normalde storage da boş alan görünür ama o alan bizim değildir. İhtiyaç duyuldukça tahsis edilir. Bu sebepten ilgili alan varsa bile böyle bir alan tahsis edilmediği için 1105 hatasının alınmasına sebebiyet vermektedir.
Bu makalede SQL Server Error Number 1105 Hatası ve Çözümünü görmüş olduk. Başka makalede görüşmek dileğiyle.
“Hakikaten onlar, Rablerine inanmış gençlerdi. Biz de onların hidayetini arttırdık. Kehf-13
Thanks for sharing a perspective that made me think a little harder. I enjoyed the discussion and the care behind your observations.