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 = ',' ;

Rate

2.2 (5)

Share

Share

Rate

2.2 (5)