[SQL Server] Arrêter automatiquement une requête SQL
DECLARE @session_id INT
DECLARE @Nbre_Session INT
DECLARE @Requete NVARCHAR(100)
SELECT @session_id=session_id,@Nbre_Session=COUNT(*) FROM
(
SELECT
ES.session_id,
ES.login_name AS LoginName,
ES.host_name AS HostName,
ST.text,
ST.dbid,
DB_NAME(ST.dbid) Base_Donnees
FROM sys.dm_exec_sessions ES
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle)AS ST
WHERE ES.host_name='serveur-sql' AND ES.original_login_name='MEKTABA\Administrateur'
AND(status='sleeping' OR status='running' OR status='SUSPENDED')
AND(DB_NAME(ST.dbid)='MaBase' OR ST.text LIKE '%MaBase%')
AND ST.text LIKE '%SELECT%'
AND ST.text NOT LIKE '%UPDATE%'
AND ST.text NOT LIKE '%INSERT%'
AND ST.text NOT LIKE '%DELETE%'
) RES GROUP BY session_id
IF @Nbre_Session>=4
BEGIN
SELECT @Requete = N'KILL ' + CAST(@session_id AS VARCHAR(50))
EXEC sp_executesql@Requete
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SqlServerProfil',
@recipients = 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@body = 'Le process serveur-sql a été suspendue',
@subject = 'Process serveur-sql suspendue',
@body_format = 'HTML',
@importance='High'
END