Technical Article

Report on existing user connections

,

This script reports gives a list of current users connected via email.  It also reports on any linked server connections lasting for more than 1 hour or 60 minutes.  Any linked server connecting for more than an hour could potentially be a hung connection and will interfere with database maintenance processes.

--// By Tung Dang 2014/10/22
--// Send Out Notificaton
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'EMAIL'
, @subject = 'User Connection Report for [SERVERNAME]'
, @query = 'SELECT SPID
, LOGINAME = cast(LOGINAME as varchar(16))
, DB = (SELECT cast(name as varchar(16)) FROM master.dbo.sysdatabases WHERE dbid = sysprocesses.dbid)
, CPU
, PHYSICAL_IO
, LAST_BATCH
FROM sysprocesses
WHERE (loginame LIKE ''User%''
OR (loginame = ''LinkServer'' and datediff(minute, last_batch, getdate()) > 60 ))
AND loginame NOT LIKE ''IgnoreUser%''
ORDER BY nt_username'
, @query_result_header = 1
--, @query_result_width = 36
--, @query_result_separator = ',' ;

Read 3 times
(2 in last 30 days)

Rate

2.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

2.2 (5)

You rated this post out of 5. Change rating