MSSQL Lock Alert

tarihinde gönderilmiş tarafından

Veritabanlarında bazı durumlarda locklar meydana gelmektedir.Örneğin index işlemlerinde online=on yapılmadığında indexe gelen sorgularda locklanma ,updatelerin selectleri(bazı durumlarda locklanmaz), insertlerinizi kilitlemesi durumlarında  ve veritabanında yapılan diğer  işlemleri locklayabilir ve siz bunu instance’ı dar boğaza düşürdüğünde ya da  uygulamacının mail, telefon ile size ulaşması durumunda öğreniyorsanız aşağıdaki t-sql tam size göre.

Aşağıdaki scriptte neler olduğundan kısaca bahsetmek istiyorum.

 Çalışmış olduğunuz  instance da ki lockların sayısını dakika da bir  hesaplayarak sizin belirlemiş olduğunuz sayıdan büyük ise , mail içeriğinde scriptin çalıştığı instance adını ,sınırı aştığına dair uyarı metini sistemde önceden tanımladığınız mail profil ismini otomatik olarak bularak sizin belirteceğiniz mail adresine  bilgilendirme maili atan scripttir.


Bu script’in düzgün çalışması  için aşağıdaki parametreleri değiştirmeniz gerekmektedir.

 

@mail_gönderilecek_kisi  ==> Lock ile ilgili bilgilendirme mailini gönderilecek hesap

@lockcount                         ==>  Bu parametrede belirttiğiniz sayıdan fazla lock olursa size bilgilendirme maili atacaktır. (default değer 100)

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'lock alert',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'lock_script',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @lockcount int,
@mail_gönderilecek_kisi nvarchar(max) =''farukerdemm@outlook.com.tr'',-- mail gönderilecek hesap
 @msj nvarchar(max) = (select @@SERVERNAME+'' instancenda belirtilen sayıdan fazla lock bulunmaktadır.Gerekli Kontrolleri sağlarmısınız?''),
 @profle nvarchar(max) =(select name from msdb.dbo.sysmail_profile)
set @lockcount=( select count(t1.resource_type)                
from
sys.dm_tran_locks t1,
sys.dm_os_waiting_tasks t2
where
t1.lock_owner_address = t2.resource_address)
if @lockcount>100  --lock sayısı
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name =@profle,  --profil adı
@recipients = @mail_gönderilecek_kisi,
@subject = ''Lock Alert'', -- konu başlığı
@body=@msj  --Mail içeriği
end',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


Yukarıdaki script’i çalıştırdığınızda lock olduğunda  aşağıdaki gibi bilgilendirme maili alacaksınız.

Loading