SQL Server Transaction Log Doluyor: ACTIVE_TRANSACTION ve ONLINE Index Rebuild - Çağlar Özenç

Merhaba arkadaşlar,

Geçenlerde bir üretim ortamında klasik ama herkesin er ya da geç başına gelen bir alarmla karşılaştım. Bu kısa yazıda o vakayı, isimleri anonimleştirerek, baştan sona anlatacağım: log’u kimin tuttuğunu nasıl bulduk, kill mi etmeli yoksa beklemeli miyiz kararını neye göre verdik, ve en önemlisi bu durumun bir daha başımıza gelmemesi için ne yapmalıyız.

Sabah sabah monitoring ekranına şu alarm düşüyor:

Transaction Log Capacity
database_name   log_size_MB   space_used_%   threshold_%   reason
APP_DB          300000        83             80            ACTIVE_TRANSACTION

Log dosyası 300 GB’a dayanmış, %83 dolu ve dolmaya devam ediyor. Böyle bir durumda çoğumuzun ilk refleksi şu olur: “Log backup çalışıyor ama boşluk açılmıyor, hemen bir backup daha alayım.” Alırsınız hiçbir şey değişmez. Çünkü asıl mesaj reason kolonunda saklı: ACTIVE_TRANSACTION. Gelin adım adım bakalım.

Neden log backup boşluk açmıyor?

FULL recovery model’de transaction log’un truncate olabilmesi (yani içindeki tamamlanmış kısmın yeniden kullanılabilir hale gelmesi) için iki şart vardır:

Log backup alınmış olması,
O bölgeyi hala tutan aktif bir transaction olmaması.

Log dosyası sanal log dosyalarından (VLF) oluşur ve truncate işlemi ancak en eski aktif transaction’a kadar ilerleyebilir. Ortada saatlerdir açık kalmış tek bir transaction varsa, ondan sonrasını backup alsanız bile o noktadan ileriye truncate edemezsiniz. Backup zinciriniz kusursuz çalışsa dahi log şişmeye devam eder.

SQL Server bunu size tek bir kolonda söyler:

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'APP_DB';

name	log_reuse_wait_desc
APP_DB	ACTIVE_TRANSACTION

log_reuse_wait_desc değeri, log’un neden yeniden kullanılamadığını söyler. Sık görülenler:

Değer	Anlamı
`NOTHING`	Sorun yok, log serbest
`LOG_BACKUP`	Log backup bekliyor (normal, backup alınca çözülür)
`ACTIVE_TRANSACTION`	Açık bir transaction log’u tutuyor
`AVAILABILITY_REPLICA`	Always On senkronizasyonu gecikmiş
`REPLICATION`	Replication log reader geride kalmış

Bizim senaryomuzda değer ACTIVE_TRANSACTION. Yani problem backup’ta değil, açık kalmış bir transaction’da. O transaction’ı bulmamız lazım.

Suçluyu bulmak

İlk durak klasik ama hala en hızlısı:

DBCC OPENTRAN('APP_DB');

Bu komut veritabanındaki en eski aktif transaction‘ın SPID’ini ve başlangıç zamanını verir. Log’u tutan neredeyse her zaman bu transaction’dır.

Ancak SPID’i öğrenmek yetmez kill kararı vermeden önce o transaction’ın ne yaptığını görmek isteriz. Bunun için aktif transaction’ları, session ve request bilgileriyle birleştiren şu sorguyu kullanıyorum:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    r.command,
    r.status                                              AS request_status,
    r.wait_type,
    r.blocking_session_id,
    t.transaction_begin_time,
    DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS open_minutes,
    st.text                                               AS sql_text
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions se ON se.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s              ON s.session_id = se.session_id
LEFT JOIN sys.dm_exec_requests r         ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
ORDER BY t.transaction_begin_time ASC;

Sonuç tablosunda onlarca satır olabilir. Ama sıralamayı transaction_begin_time ASC yaptığımız için en tepedeki satır en eski yani suçlu adayımız. Vakamızda tablo şöyleydi (kısaltılmış):

SPID	program_name	status	command	db	open_min	sql_text
448	SQLAgent – JobStep	running	ALTER INDEX	APP_DB	200	REBUILD PARTITION… ONLINE=ON
503	DatabaseMail	suspended	DELETE	msdb	3	sp_readrequest (queue)
606	.NET SqlClient	sleeping	—	—	1	NULL
131	SQLAgent – JobStep	running	SELECT INTO	APP_DB	0	ETL prosedürü
527	SSIS	running	DELETE	APP_DB	0	Arşiv temizleme
138	AlwaysOn Dashboard	running	SELECT INTO	master	0	Monitoring sorgusu

Tabloyu okumayı öğrenmek burada kritik. Satırların çoğu gürültü:

msdb / master üzerindekiler (503, 138) bizim veritabanımızı ilgilendirmiyor.
sleeping + sql_text NULL olanlar (606 ve benzerleri) — connection pool’da bekleyen boş bağlantılar, log tutmuyorlar.
open_minutes = 0 olan taze işler (131, 527) — henüz yeni başlamışlar, 3 saatlik log şişmesinin sebebi olamazlar.

Geriye tek bir satır kalıyor:

SPID 448 – APP_DB üzerinde, 200 dakikadır açık, bir SQL Agent bakım işi tarafından çalıştırılan ONLINE index rebuild:

ALTER INDEX [IX_LargeTable_1] ON [dbo].[LargeTable]
REBUILD PARTITION = N
WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 32, RESUMABLE = OFF);

İşte log’u tutan bu. Devasa, partition’lı bir tablonun tek bir partition’ını ONLINE modda yeniden inşa eden bir bakım işi. Ve tam da burada işin en öğretici kısmı başlıyor.

Neden ONLINE index rebuild bir “log canavarı”dır?

ONLINE index rebuild, adı gibi tabloyu kilitlemeden çalışır uygulama okumaya/yazmaya devam edebilir. Ama bunun bir bedeli var:

İşlem tek, uzun ömürlü bir transaction içinde döner. Partition ne kadar büyükse transaction o kadar uzun açık kalır.
Bu süre boyunca log truncate olamaz, çünkü en eski aktif transaction budur.
MAXDOP = 32 gibi yüksek paralellik ve büyük bir partition, saatlerce sürebilen ve yüz GB’larca log üreten bir iş demektir.

Yani rebuild devam ettiği sürece log dosyası tek yönde gider: yukarı. Backup zinciriniz mükemmel çalışsa bile.

Karar anı: `KILL` mü, beklemek mi?

Alarm çalıyor, log %83’te ve büyüyor. İçgüdü “hemen kill et” diyor. Ama durun çünkü bu bir ONLINE rebuild ve dikkat: RESUMABLE = OFF.

KILL 448 dediğinizde ne olur?

Partition rebuild’in tamamı rollback edilir. RESUMABLE = OFF olduğu için kaldığı yerden devam edemez; yaptığı iş çöpe gider, sıfırdan başlaması gerekir.
Rollback’in kendisi de log üretir ve genellikle işin ne kadar ilerlediğiyle orantılı sürede tamamlanır. Yani yarısına gelmiş bir işi kill ederseniz, rollback de epey sürebilir.
En kritiği: rollback bitene kadar ACTIVE_TRANSACTION durumu devam eder. Kill ettiğiniz an log rahatlamaz; hatta rollback sırasında bir miktar daha şişebilir. Log ancak rollback tamamlanıp transaction gerçekten kapandıktan sonra boşalır.

Dolayısıyla “kill = anında rahatlama” beklentisi yanlıştır. Karar, tek bir soruya bağlıdır:

Diskte / log dosyasında büyümek için yer var mı?

Senaryo A — Yer varsa (tercih edilen): Dokunmayın, bitmesini bekleyin. Rebuild commit olunca normal log backup zinciriniz log’u truncate eder ve doluluk düşer. Gereksiz bir rollback maliyetine ve işin baştan çalışmasına hiç girmezsiniz. Bu en temiz çözümdür.

Beklerken tek yapmanız gereken işi izlemek:

SELECT
    percent_complete,
    DATEDIFF(MINUTE, start_time, GETDATE())      AS running_min,
    estimated_completion_time / 1000 / 60.0      AS est_remaining_min,
    status,
    wait_type
FROM sys.dm_exec_requests
WHERE session_id = 448;

percent_complete artıyorsa iş sağlıklı ilerliyordur. estimated_completion_time size kabaca ne kadar kaldığını söyler. Bu arada normal log backup’larınızı da almaya devam edin — açık transaction’a ait olmayan kısımları yine de truncate eder, şişme hızını yavaşlatır.

Senaryo B — Yer daralıyorsa / disk dolmak üzereyse: O zaman kill etmekten başka çare yok. Ama kill’i döngüde log backup ile desteklemelisiniz, çünkü rollback sırasında da log üretilir.

KILL 448;

Rollback ilerlemesini takip edin:

KILL 448 WITH STATUSONLY;

Ve rollback boyunca, 2-3 dakikada bir log’u zorla boşaltın:

BACKUP LOG APP_DB TO DISK = 'X:\Backup\APP_DB_log_rollback.trn';

Doluluğu her adımda kontrol edin:

DBCC SQLPERF(LOGSPACE);

Bizim vakamızda diskte yer vardı; dolayısıyla doğru hamle beklemekti. (İş operasyonel bir kararla durduruldu ve rollback tamamlanınca log backup zinciri boşluğu geri kazandı ama “yer varken bekle” prensibi hala geçerli.)

Kill sonrası teyit

Ne yaparsanız yapın, kapatmadan önce durumu doğrulayın:

-- Log artık serbest mi?
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'APP_DB';
-- Artık 'ACTIVE_TRANSACTION' görmemeli; 'NOTHING' veya 'LOG_BACKUP' olmalı

-- Doluluk düştü mü?
DBCC SQLPERF(LOGSPACE);

log_reuse_wait_desc hala ACTIVE_TRANSACTION gösteriyorsa, rollback devam ediyor demektir — sabırla bekleyin, ikinci bir işi kill etmeye kalkışmayın.

Asıl çözüm: Bir daha bu duruma düşmemek

Yangını söndürmek güzel, ama aynı yangının her hafta çıkması iyi bir DBA’in kabul edeceği bir şey değildir. Bu vakadaki kök sebep tek seferlik bir kaza değil, bir bakım işinin tasarımıydı. Aynı büyük partition, bir sonraki bakım penceresinde yine aynı şekilde log’u şişirecekti.

1. `RESUMABLE = ON` kullanın

SQL Server 2017+ ile gelen resumable online index rebuild, oyunun kurallarını değiştirir:

ALTER INDEX [IX_LargeTable_1] ON [dbo].[LargeTable]
REBUILD PARTITION = N
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES);

Bunun getirisi:

İşi PAUSE ile duraklatabilirsiniz; duraklatınca transaction kapanır ve log truncate olabilir. Log backup alıp boşluk açtıktan sonra RESUME ile kaldığınız yerden devam edersiniz.
MAX_DURATION ile işi otomatik parçalara böler; her parça arasında log nefes alır.
Kill etmek zorunda kalsanız bile baştan başlamazsınız — kaldığı yerden devam eder.

Yani log’u dev bir transaction’da tutmak yerine, kontrollü küçük dilimlere bölmüş olursunuz.

2. Bakım stratejisini gözden geçirin

Her seferinde REBUILD yerine, fragmentasyon eşiğine göre REORGANIZE / REBUILD ayrımı yapın (Ola Hallengren’in bakım çözümü bunu hazır sunar). REORGANIZE daha küçük transaction’larda ilerler.
Partition bazında rebuild’i off-peak ve log backup sıklığının yüksek olduğu bir pencereye alın.
Devasa tablolarda tüm partition’ları tek seferde değil, birer birer / dönüşümlü rebuild edin.

3. Proaktif izleme

log_reuse_wait_desc değerini periyodik olarak izleyin. ACTIVE_TRANSACTION‘ın uzun süre takılı kaldığı durumları alarm haline getirin %83’e gelmeden, %50’de haberdar olun.

Özetleyecek olursak;

Bir gün aynı alarmla karşılaşırsanız, sıra şu:

Teşhisi doğrula: sys.databases.log_reuse_wait_desc → ACTIVE_TRANSACTION mı?
Suçluyu bul: DBCC OPENTRAN + aktif transaction DMV sorgusu. En eski, en uzun açık transaction’ı ara.
Ne olduğunu anla: ONLINE index rebuild mi, orphaned bir uygulama transaction’ı mı, uzun bir ETL mi? Karar buna göre değişir.
Kill kararını doğru ver:
- Diskte yer varsa → bekle, rollback maliyetine girme.
- Yer daralıyorsa → kill + döngüde log backup.
Teyit et: log_reuse_wait_desc = NOTHING/LOG_BACKUP ve DBCC SQLPERF(LOGSPACE) düştü mü?
Kökten çöz: Büyük tablolarda RESUMABLE = ON, bölünmüş bakım pencereleri ve proaktif izleme.

ACTIVE_TRANSACTION gördüğünüzde attığınız ilk adımın “bir backup daha alayım” olmaması çözümün yarısı budur. Diğer yarısı, o açık transaction’ın ne olduğunu anlamadan asla kill tuşuna basmamaktır. Diskte yer varken sabredip beklemek, çoğu zaman en olgun çözümdür.

Sorularınızı bekliyorum, kolay gelsin.

Sık Sorulan Sorular

Transaction log ACTIVE_TRANSACTION yüzünden dolarken log backup neden boşluk açmaz?

FULL recovery model’de log ancak en eski aktif transaction’a kadar truncate olabilir. Saatlerdir açık bir transaction varsa, ondan sonrasını backup alsanız bile o noktadan ileriye boşluk açılamaz. log_reuse_wait_desc = ACTIVE_TRANSACTION ise sorun backup’ta değil, açık transaction’dadır.Log’u tutan aktif transaction nasıl bulunur?

DBCC OPENTRAN en eski aktif transaction’ın SPID’ini verir. Detay için sys.dm_tran_active_transactions, sys.dm_tran_session_transactions, sys.dm_exec_sessions/requests ve sys.dm_exec_sql_text DMV’lerini transaction_begin_time‘a göre artan sıralayın; en üstteki en eski, yani suçlu adayıdır.Uzun süren ONLINE index rebuild’i kill mi etmeli yoksa beklemeli mi?

Diskte/log dosyasında yer varsa beklemek en temizidir: rebuild commit olunca log backup zinciri truncate eder. Yer daralıyorsa kill edip rollback boyunca 2-3 dakikada bir log backup alın. Kill anında rahatlamaz; rollback bitene kadar ACTIVE_TRANSACTION devam eder ve rollback de log üretir.Bu durum tekrar yaşanmasın diye kalıcı çözüm nedir?

Büyük tablolarda rebuild’i RESUMABLE = ON ve MAX_DURATION ile çalıştırın; PAUSE edince transaction kapanır, log truncate olur, sonra RESUME ile devam edersiniz. Ayrıca REORGANIZE/REBUILD eşiği kullanın, bakımı off-peak’e alın ve log_reuse_wait_desc‘i proaktif izleyin.Bu yazıdaki veritabanı, sunucu ve nesne isimleri anonimleştirilmiştir. Senaryo gerçek bir üretim vakasına dayanmaktadır.