Les connexions utilisateurs

Pour la réalisation de certaines tâches administratives, il est nécessaire de connaître les utilisateurs actuelllement connectés sur une base de données. cette liste peut être établie grace à la requête suivante:

select host_name,program_name,login_name
  from sys.dm_exec_sessions
  where is_user_process=1;
			

La restriction sur is_user_process permet de ne lister que les processus utilisateur. En effet il existe des processus système qui travaillent sur la base. Ces processus sont propres à SQL Server et il n'est pas possible d'intervenir dessus.

Les colonnes host_name, program_name et login_name fournissent les informations relatives à la machine, le programme et la connexion utilisés pour se connecter.

Il est possible de mettre fin à des connexions utilisateurs grâce à l'instruction KILL qui est détaillée à la fin de cet article.

Les utilisateurs connectés au serveur

L'exemple suivant recherche les utilisateurs connectés au serveur et retourne le nombre de sessions pour chaque utilisateur.

SELECT login_name ,COUNT(session_id) AS "nombre de sessions" 
FROM sys.dm_exec_sessions 
GROUP BY login_name;
			

Les sessions inactives ayant des transactions ouvertes

Toujours à partir de sys.dm_exec_sessions il est possible de lister les connexions inactives (c'est à dire qui n'ont pas de requête en cours d'exécution) et qui pour autant ont une transation en cours. Ces transactions sont à l'origine des problèmes de verrouiallage.

select s.* 
from sys.dm_exec_sessions as s
where exists   (
    select * from sys.dm_tran_session_transactions as t
    where t.session_id = s.session_id)
    and not exists (
    select * from sys.dm_exec_requests as r
    where r.session_id = s.session_id  );

Les curseurs longs

les curseurs mobilisent également de nombreuses resources aussi est il interessant de lister les curseurs dont les temps d'exécution (ouverture) sont particulièrement long. Dans l'exemple ci dessous la restriction DATEDIFF(mi, c.creation_time, GETDATE()) portent sur le nombre de minutes (mi) et permet de ne lister que les curseurs ouverts depuis plus que 5 minutes.

select creation_time ,cursor_id ,name ,c.session_id ,login_name 
from sys.dm_exec_cursors(0) as c inner join sys.dm_exec_sessions as s 
  on c.session_id = s.session_id 
where DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

Terminer une session

L'instruction KILL permet de terminer (tuer) des sessions utilisateur. En effet il n'est pas possblie de mettre fin à des sessions systèmes. Il n'est pas possible, non plus, de mettre fin à des processus en cours d'exécution de procédure stockée étendue. On ne peut pas, bien sur, mettre fin à son propre processus.

En utilisant l'option WITH STATUSONLY de l'instruction KILL, la progression de l'annulation de la session annulée est affiché.

Par exemple en exécutant la requête suivante:

select session_id,host_name,program_name,login_name
  from sys.dm_exec_sessions
  where is_user_process=1;
			

On peut mettre fin à la session identifiée (session_id) par le numéro 57 alors il faudra utiliser l'instruction suivante:

kill 57