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.
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
