[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

Ajouter un Commentaire


Code de sécurité
Rafraîchir