Bu makalede sleeping modda olan sessions’ları otomatik olarak kill etme komutunu görmüş olacağız. Aynı zamanda hangi session kill edilmişse tabloya kaydetme işlemi yapmış 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.
İlk olarak kill edilen sessionları kaydedeceğimiz tablomuzu oluşturuyoruz.
USE [DBAMON]
GO
CREATE TABLE [dbo].[KilledInActiveSessions](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SPID] [int] NOT NULL,
[SQLHANDLE] [varbinary](64) NULL,
[SQLTEXT] [nvarchar](max) NULL,
[COLLECTIONTIME] [datetime] NOT NULL,
[LOGINNAME] [varchar](256) NULL,
[HOSTNAME] [varchar](128) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[KilledInActiveSessions] ADD DEFAULT (getdate()) FOR [COLLECTIONTIME]
GOTablomuzu oluşturduktan sonra aşağıdaki kodumuz ile sleeping modunda olan sessionlar kill edilip ayrıca kill edilen ifadeleri oluşturmuş olduğumuz tabloya kaydetmiş olacağız.
DECLARE @sql NVARCHAR(100);
declare @text nvarchar(max);
declare @sqlHandle varbinary(64);
declare @spid int;
declare @collettionDate datetime;
declare @loginname varchar(256);
declare @hostname varchar(128);
select @collettionDate = getdate();
DECLARE CurSPID CURSOR FAST_FORWARD
FOR
SELECT spid ,sql_handle,text,login_name,host_name
FROM master.dbo.sysprocesses p (NOLOCK)
inner join sys.dm_exec_sessions s (NOLOCK) on (p.spid = s.session_id)
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS t
WHERE spid > 50
AND p.status = ''sleeping''
-- p.cmd='AWAITING COMMAND'
and is_user_process = 1 AND open_transaction_count = 0
AND spid <> @@SPID -- Kendi bağlantımızı hariç tutuyoruz
and login_name NOT IN (
'YCL2025\khYNS00', 'NT AUTHORITY\SYSTEM', 'S23',
'YCL2025\S01', 'YCL2025\S02',
'YCL2025\TS03', 'YCL2025\KS04', 'YCL2025\TS05',
'YCL2025\YNS1', 'YCL2025\SqlService')
AND spid <> @@SPID
AND last_batch < DATEADD(MINUTE, -10, GETDATE())
;
OPEN CurSPID;
FETCH NEXT FROM CurSPID INTO @spid,@sqlHandle,@text,@loginname, @hostname ;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT ''Killing SPID '' + CONVERT(VARCHAR, @spid);
BEGIN TRY
IF not EXISTS(select session_id from sys.dm_exec_requests where session_id = @spid)
BEGIN
SET @sql = ''KILL '' + CONVERT(VARCHAR, @spid);
EXEC(@sql);
INSERT INTO [DBAMON].[dbo].[KilledInActiveSessions]
([SPID]
,[SQLHANDLE]
,[SQLTEXT]
,[COLLECTIONTIME]
,LOGINNAME
,HOSTNAME)
VALUES
(@spid
,@sqlHandle
,@text
,@collettionDate
,@loginname
,@hostname);
END
END TRY
BEGIN CATCH
PRINT ''HATA: '' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM CurSPID INTO @spid,@sqlHandle,@text,@loginname, @hostname ;
END;
CLOSE CurSPID;
DEALLOCATE CurSPID;Not: Sleeping modunda olan session otomatik olarak AWAITING COMMAND moduna geçmektedir. Yukarıdaki kodda sadece sleeping mod belirtilmesi yeterlidir. AWAITING COMMAND işlemi bittiğini yeni bir komut beklediğini söylemektedir. Yeni gelen connectionları ve aktif IIS connectionlarını kapatmamak için hemen çalıştırmak gerekiyor ama 10 dakikalık AWAITING COMMAND modda olanlar kill edilebilir buda sıkıntıya sebebiyet verir. IIS ler connectionları alır awaiting command modunda bekler. Bu durumda anlık olarak open tran count sıfıra çekebilir.
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
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.
İkinci bir sleepin job’mız:
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.
Başka bir makalede görüşmek dileğiyle..
“De ki: Ey Rabbim! İlmimi artır.” Taha-114
