Always ON’daki Primary Veritabanının Log Dosyasının ACTIVE_TRANSACTION Nedeniyle Dolması

Bu sorun Always On’da bugüne kadar yaşadığım ender problemlerden biri. Primary veritabanının log dosyası bir şekilde doluyor. Normalde log backup alınmazsa log dosyası dolar fakat burdaki problem biraz başka.

Log dosyasının dolma nedenini aşağıdaki script yardımıyla görebilirsiniz.

select log_reuse_wait_desc from sys.databases where name=’Veritabanı İsminiz‘

Makalemize konu olan problemde bu sorgu sonucu ACTIVE_TRANSACTION olarak döner. Eğer sonuç sizde replication olarak dönüyorsa “Log Dosyasının Truncate ya da Shrink Olmaması ve Diski Doldurması Durumu” isimli makalemi okumalısınız.

Makalemize geri dönecek olursak, bu sorun nedeniyle log dosyası log backup alındıktan sonra truncate olmadığı için şişmeye başlar. Log backup’ın log dosyası üzerine etkisini incelemek için “SQL Server Transaction Log Nedir” isimli makaleye göz atmak isteyebilirsiniz.

Aslında ACTIVE_TRANSACTION’ın anlamı veritabanında aktif bir transaction var ve bu transaction log dosyasını tamamını dolduruyor anlamına gelir. Gerçekten böyle bir durum olup olmadığını anlamak için aşağıdaki sorguyu çalıştırıp uzun süredir çalışan bir sorgu varsa uygulamacı ile irtibatlı bir şekilde sorguyu kill edebilirsiniz.

SELECT sp.spid,st.text,sp.status,sp.login_time,sp.last_batch,sp.hostname,sp.loginame 
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st
WHERE open_tran = 1 and sp.dbid=DB_ID('Veritabanı İsmini Buraya Yazmalısınız')
order by login_time asc

Eğer aktif transaction olmadığı halde ACTIVE_TRANSACTION nedeniyle bu sorunu yaşıyorsanız ve veritabanının always on’a dahil bir veritabanıysa aşağıda belirttiğim adımları uygulamanız sorununuzu çözecektir.

Bu sorun ilk başıma geldiğinde veritabanını AG’den çıkarıp yeniden alarak çözdüm. Fakat sorun kendini birkaç kez tekrar etti. Veritabanı boyutları da büyük olduğu için her seferinde AG’den çıkarıp almak istemedim.

Bu yüzden şöyle bir yöntem denedim ve işe yaradı.

Öncelikle primary veritabanında aşağıdaki gibi ilgili veritabanına sağ tıklayarak Suspend Data Movement Diyoruz.

Daha sonra log backup alıyoruz. Ve ardından yukardaki ekranda yaptığımız işlemi yeniden yaparak bu sefer Resume Data Movement diyoruz.

Bu işlemi yaptıktan sonra bir süre hala ACTIVE_TRANSACTION sorunu devam eder. Secondary veritabanına gidip senkron durumuna baktığınızda aşağıdaki gibi Not Synchronizing / In Recovery olarak görürsünüz.

Secondary veritabanının log dosyasını açtığınızda ise aşağıdaki gibi veritabanını kendini recovery etmeye çalışıyordur.

Yukardaki resimde gördüğünüz Recovery işlemi bittiğinde primary veritabanına tekrar geçip aşağıdaki script’i tekrar çalıştırın.

select log_reuse_wait_desc from sys.databases where name=’Veritabanı İsminiz’

Sonuç hala ACTIVE_TRANSACTION gözüküyorsa veritabanınız hala geriden geliyor demektir. Benim karşıma bu problem geldiğinde ag senkron ayardayken secondary veritabanı synchronizing durumundaydı. Normalde secondary veritabanınında synchronized olması gerekiyordu.

AG üzerinde sağ tıklayarak SHOW Dashboard diyoruz ve aşağıdaki resimde işaretlenmiş alandan Add/Remove Columns’a tıklayarak Last Hardened Time ve Last Commit Time’ı seçiyoruz.

Last Hardened Time primary’den secondary’e en son log kaydının en son ne zaman gönderildiğini gösteriyor. Baktığımda bu saat günceldi.

Last Hardened Time’da ise nerdeyse 2.5 gün geriden geldiğini gördüm. Dashboard ekranı otomatik güncellediği için Last Commit Time’ın sürekli ilerlediğini göreceksiniz. Bir süre sonra Last Commit Time güncel’i yakalarken secondary veritabanı da Synchronized duruma geçecek ve sorununuz düzelecektir.

Sorguyu tekrar çalıştırdığınız artık sonuç NOTHING olarak gözükecektir ve transaction log dosyası shrink edilebilir olacaktır. Transaction dosyasını shrink ederek işlemlerinizi tamamlayabilirsiniz. Transaction log dosyasını shrink etmek için “SQL Server Log Dosyasını Shrink Etmek” isimli makaleden faydalanabilirsiniz.

Eğer log dosyasında oluşan problemi önceden tespit edip gerekli önlemleri almak isterseniz “SQL Server Log Dosyası Problem Tespiti” isimli makaleyi okumanızı tavsiye ederim.