SQL Server Log Dosyası Problem Tespiti

SQL Server’da bazen log dosyası bazı sebeplerle şişebilir. Örneğin log backup alınmadığı için ya da bazı problemler nedeniyle. Aşağıdaki makalelerde log dosyasının neden dolabileceği hakkında makaleler bulabilirsiniz.

“Log Dosyasının Truncate ya da Shrink Olmaması ve Diski Doldurması Durumu“,

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

Eğer log dosyasının dolduğunu ve bir problem olduğunu önceden bilirseniz log dosyası çok şişmeden müdahale edebilirsiniz. Bu yüzden aşağıdaki script’i kendi ortamımız için hazırlamıştık sizinle de paylaşmak istedim.

Aşağıda,  boyutu 500 GB’ın üstünde olan ve shrink edilemeyecek kadar dolu olan log dosyalarını ilgili kişilere mail atan script’i bulabilirsiniz. Mailadresi yazan yere ilgili mail adresini yazmalısınız. SQL Server sunucunuzun mail atabilmesi için Database Mail konfigürasyonu yapmalısınız. “Database Mail Konfigurasyonu” isimli makale size yardımcı olabilir.

DECLARE @VeritabaniAdi VARCHAR(250)
DECLARE @LogBuyuklugu2 DECIMAL(19,2)
DECLARE @KullanılanLogBuyuklugu2 DECIMAL(19,2)
DECLARE @HTML  NVARCHAR(MAX)
DECLARE @Sql NVARCHAR(MAX)
DECLARE @Sayac tinyint

/*Cursor'a bir isim veriyoruz*/

SET @HTML =
    N'<H1>Log Dosyası Kontol Edilmesi Gereken Veritabanları</H1>' + 
    N'<table border="1">' + 
    N'<tr><td>Veritabanı Adı</td><td>Log Büyüklüğü</td><td>Kullanılan Log Büyüklüğü</td></tr>' ;
SET @Sayac=0
DECLARE LogAlertCrs CURSOR
FOR

/*Cursor'ın dolaşacağı kayıt kümesini belirten select cümleceği 
sadece instance üzerinde always on varsa primary veritabanlarını 
ve ayrıca always on olmayan diğer veritabanlarını getirir*/

SELECT '['+name+']' FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)

OPEN LogAlertCrs
FETCH NEXT FROM LogAlertCrs INTO @VeritabaniAdi

/*WHILE @@FETCH_STATUS=0 Cursorda dolaşacak kayıt kalmayana kadar bir sonraki kayda geçerek cursor'ı döndürmeye devam et demek*/

WHILE @@FETCH_STATUS =0
BEGIN 

Select @Sql = 'Use ' +@VeritabaniAdi + N';

SELECT @LogBuyuklugu=CAST(sum(size)/131072.0 AS decimal(19,2)) ,
@KullanılanLogBuyuklugu=CAST(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/131072.0 AS DECIMAL(19,2)) 
from sys.database_files where type=1 group by type'

EXEC sp_executesql @Sql,@Params= N'@LogBuyuklugu DECIMAL(19,2) OUTPUT, @KullanılanLogBuyuklugu DECIMAL(19,2) OUTPUT'
,@LogBuyuklugu = @LogBuyuklugu2 OUTPUT
,@KullanılanLogBuyuklugu = @KullanılanLogBuyuklugu2 OUTPUT; 


IF(@LogBuyuklugu2>=500 AND @LogBuyuklugu2-@KullanılanLogBuyuklugu2<1)
BEGIN
SET @Sayac+=1;
SET @HTML+=N'<tr><td>'+@VeritabaniAdi+'</td><td>'+CAST(@LogBuyuklugu2 AS nvarchar(50))+
'</td><td>'+CAST(@KullanılanLogBuyuklugu2 AS nvarchar(50))+'</td></tr>';

END
 FETCH NEXT FROM LogAlertCrs INTO @VeritabaniAdi
END       
SET @HTML+=N'</table>' ;  

if(@Sayac>0)
BEGIN       
EXEC msdb.dbo.sp_send_dbmail
@recipients='mailadresi',
@subject = 'Log Dosyası Kontrol',
@body = @HTML,
@body_format = 'HTML' ;
END

/*CLOSE ve DEALLOCATE komutları ile Cursor'ı kapatıyoruz*/

CLOSE LogAlertCrs
DEALLOCATE LogAlertCrs