Kill All Sleeping Sessions

Bu makalede belirli bir süreden sonra sleeping modda olan sessions’ları otomatik olarak kill etme komutunu görmüş olacağız. Bu kod bloğumuzu bir job aracılığıyla sürekli haline getirebiliriz.

Sessionsların sleeping modda olduğunu görmekteyiz.

Aşağıdaki komut sayesinde 10 dakikadan uzun süren sessions’ları kill eden komutu görmekteyiz. Aktif bağlantıları kesmez, bu yüzden sistem üzerinde çalıştırmak güvenlidir. Sadece kullanıcı bağlatıları üzerinde işlem yapmaktadır.

DECLARE @user_spid INT;

DECLARE CurSPID CURSOR FAST_FORWARD
FOR
SELECT spid
FROM master.dbo.sysprocesses (NOLOCK)
WHERE spid > 50 -- Sistem iş parçacıklarını hariç tut
AND status = 'sleeping' -- Yalnızca "sleeping" işlemler
AND DATEDIFF(MINUTE, last_batch, GETDATE()) >= 10 -- 10 dakikadan uzun süredir boşta olanlar
AND open_tran_count=0     --rollback olmaması icim acik transaction'da yapmiyoruz
AND spid <> @@SPID -- Kendi bağlantımızı hariç tutuyoruz
AND spid IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1) -- Sadece kullanıcı süreçlerini işleme alıyoruz.
AND spid IN (
    SELECT session_id 
    FROM sys.dm_exec_sessions 
    WHERE login_name NOT IN ('DO1\serviskullanıcı', 'NT AUTHORITY\SYSTEM')); -- Belirli kullanıcıları hariç tutuyoruz. GENELDE SYSADMINLER YAZILIR.

OPEN CurSPID
FETCH NEXT FROM CurSPID INTO @user_spid

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'Killing ' + CONVERT(VARCHAR, @user_spid);
    EXEC('KILL ' + @user_spid); 

    FETCH NEXT FROM CurSPID INTO @user_spid;
END

CLOSE CurSPID
DEALLOCATE CurSPID;
GO

login_name NOT IN komutunu çalıştırmazsak sysadmin kullanıcıları bile hata mesajı almaktadır.

Yukarıdaki komutta open_tran_count değerine değinelim. Kodumuzun olmazsa olmaz bir parçası olarak karşımıza çıkmaktadır.

open_tran_count SQL Server’da bir session (SPID) üzerinde kaç tane açık (commitlenmemiş) transaction olduğunu gösterir.

Bu değer:

0 ise: Hiç açık transaction yoktur.
1 veya daha fazlaysa: En az bir açık transaction vardır.

sp_whoIsactive ekranında görebiliriz.

sp_WhoIsActive çıktısında her session’ın open_tran_count değeri listelenir. Bu bilgi:

Hangi oturumların transaction açıp commit etmediğini,
Hangi işlemlerin olası lock (kilit) veya resource contention (kaynak sıkışıklığı) yaratabileceğini anlamak için kullanılır.

sp_whoIsactive çekildikten sonra open_tran_count ifadesinin yanında status bölümünde oturumun durumu görülmektedir. Şimdi bu ifadelerle birlikte açıklamamızı yapalım.

Sleeping modda ve oturumda open_tran_count > 0 ise, commit edilmemiş işlemler var demektir. Bu gibi durumlarda incelenmesi gerekmektedir.

Bir oturumu KILL komutuyla sonlandırmadan önce:

open_tran_count = 0 → işlem güvenli şekilde sonlandırılabilir. Lock yoktur.
open_tran_count > 0 → hala açık transaction olabilir. Kill edildiğinde rollback işlemi başlar, bu da zaman alabilir ve sunucuyu yorabilir.

Bu nedenle SLEEPING oturumları rastgele kill etmek yerine, open_tran_count’e bakılır:

🔸 Eğer open_tran_count = 0 ise → kill genelde risksizdir.
🔸 Eğer open_tran_count > 0 ise → neden açık bırakıldığını incelemek gerekir (kod hatası, bağlantı sorunu vs.).

open_tran_count = 2 olunca Bu, bir session (SPID) içinde iki tane iç içe (nested) veya ayrı transaction açılmış ancak henüz commit veya rollback edilmemiş demektir.

BEGIN TRANSACTION -- open_tran_count = 1

    -- bazı işlemler

    BEGIN TRANSACTION -- open_tran_count = 2
        -- bazı işlemler

    -- Henüz hiçbir COMMIT veya ROLLBACK yok

Yukarıdaki script’i çalıştırdıktan sonra oturum süresi 10 dk olan sessionsların kill edildiğini görmüş oluyoruz.

sp_whoIsactive komutu ile Sleeping modda olan oturumların kill edildiğini görmüş oluyoruz.

Sleeping modundaki bağlantıları KILL etmek, SQL Server performansı ve kaynak yönetimi açısından bazı avantajlar sağlayabilir.

1. Kaynak Kullanımını Azaltır.

Her açık bağlantı bir miktar bellek (RAM) ve CPU kaynağı tüketir. Uzun süredir kullanılmayan “sleeping” bağlantıları kapatarak bu kaynakları boşa çıkartabilirsiniz. Bu sayede yeni gelen sorgular için daha fazla sistem kaynağı ayrılabilir.

2. Deadlock Riskini Azaltır.

Sleeping oturumlar bazen lock tutabilir. Bir sorgu uzun süre sleeping modda kalırsa, başka işlemlerin çalışmasını engelleyebilir ve deadlock oluşturabilir. Gereksiz sleeping bağlantıları kapatarak kilitlenmeleri azaltabilirsiniz.

3. TempDB ve Log Kullanımını Azaltır.

Sleeping moddaki oturumlar bazen TempDB veya transaction log kullanıyor olabilir. Özellikle büyük transaction’lar açılmışsa, oturum “sleeping” olsa bile rollback bekliyor olabilir. Bu bağlantıları temizleyerek gereksiz disk kullanımını azaltabilirsiniz.

4. Performansı ve Yanıt Sürelerini İyileştirir.
Çok sayıda sleeping oturum SQL Server’ın performansını düşürebilir. Özellikle yüksek trafikli sistemlerde, bu bağlantıları temizlemek sorguların daha hızlı çalışmasını sağlayabilir.

Yukarıdaki tanımladığımız kill jobı bazen hata mesajı verebilir.

Hata mesajının olası nedenleri:
1.SPID kill edilirken oturum zaten kapanmış olabilir.
• KILL komutu, hedef SPID o an canlı değilse hata fırlatır:
“Msg 6101, Level 16, State 1, Line 1: Process ID <spid> is not an active process ID.”
2.Yetki problemi olabilir
• Job SQL Agent servis hesabı ile çalışıyorsa, bu hesabın KILL yetkisi olmayabilir.
3.Başka oturumlar bu SPID’lere bağlı işlemler yapıyor olabilir
• Örneğin: bir transaction varsa veya başka bir oturum tarafından kilitlenmişse, KILL başarısız olabilir.

Güncellenmiş kodumuz:

DECLARE @user_spid INT;
DECLARE @sql NVARCHAR(100);

DECLARE CurSPID CURSOR FAST_FORWARD
FOR
SELECT spid
FROM master.dbo.sysprocesses (NOLOCK)
WHERE spid > 50 
AND status = 'sleeping' 
AND spid <> @@SPID 
AND last_batch < DATEADD(MINUTE, -5, GETDATE()) 
AND spid IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND open_transaction_count = 0 ) 
AND spid IN (
    SELECT session_id 
    FROM sys.dm_exec_sessions 
    WHERE login_name NOT IN (
        'YCL2025\khYNS00', 'NT AUTHORITY\SYSTEM', 'S23', 
        'YCL2025\S01', 'YCL2025\S02', 
        'YCL2025\TS03', 'YCL2025\KS04', 'YCL2025\TS05',
        'YCL2025\YNS1', 'YCL2025\SService')
);

OPEN CurSPID;
FETCH NEXT FROM CurSPID INTO @user_spid;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'Killing SPID ' + CONVERT(VARCHAR, @user_spid);

    BEGIN TRY
        SET @sql = 'KILL ' + CONVERT(VARCHAR, @user_spid);
        EXEC(@sql);
    END TRY
    BEGIN CATCH
        PRINT 'HATA: ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM CurSPID INTO @user_spid;
END;

CLOSE CurSPID;
DEALLOCATE CurSPID;

Hatalı SPID kill denemeleri job’ı düşürmez, hata varsa sadece log’a yazılır.
• Böylece job başarılı olarak tamamlanır, sadece kill edilemeyen SPID’ler varsa logda görünür.

NOT: Sorgunuz opentransactioncount = 0 olan oturumları seçiyor, ancak bu her zaman güvenilir olmayabilir. sys.dmexecsessions görünümündeki opentransactioncount bazen gerçek durumu tam yansıtmayabilir. insert update veya delete işlemi devam ederken oturumun sleeping olarak işaretlenmesi Uzun süren batch işlemleri sırasında geçici sleeping durumu oluşabilir. Bu sebepten bağlantı kopabilir. bunun için yukarıdaki last_batch < DATEADD(MINUTE, -5, GETDATE()) ifadesinin eklenmesi gerekmektedir.

Not: Ekstradan sorgularımızı bir tabloya kaydetmek istersek iki komutu kullanarak gerçek değerleri değişkenlere atabiliriz. select*from sys.sysprocesses -select*from sys.dm_exec_sql_text(plan_handle_Değeri) ifadeleridyir.

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

“De ki: Ey Rabbim! İlmimi artır.” Taha-114

Bir yanıt yazın