Database connection Monitoring

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT *
 
FROM sys.dm_exec_sessions s
 
LEFT OUTER JOIN sys.dm_exec_connections c
 
ON s.session_id = c.session_id
 
LEFT OUTER JOIN sys.dm_exec_requests r
 
ON c.connection_id = r.connection_id
 
WHERE s.session_id > 50
 





USE [DBA]

USE [DBA]

GO

/****** Object:  Table [dbo].[Connection_details]    Script Date: 05/25/2017 15:40:24 ******/

--drop table [Connection_details]

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Connection_details]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Connection_details](

      [DBName] [nvarchar](50) NULL,

      [loginname] [nvarchar](50) NULL,

      [numberofconnections] [nvarchar](50) NULL,

      [login_time] [datetime] NULL,

      [version] bigint

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

 

Declare @version bigint

 

 

select * from [Connection_details]

 

 if @@ROWCOUNT = 0

 begin

 select @version=1

 end

 

print @version

 

select * from [Connection_details]

 

if @@ROWCOUNT > 1

begin

 

select top(1) @version= [version] from  [Connection_details] order by [version] desc

 

set @version=@version+1

 

End

 

print @version

 

INSERT INTO [DBA].[dbo].[Connection_details]

           ([DBName]

           ,[loginname]

           ,[numberofconnections]

           ,[login_time]

           ,[version])

          

SELECT DB_NAME(dbid) AS DBName,loginame,COUNT(dbid) AS NumberOfConnections,login_time,@version

FROM    sys.sysprocesses

where spid>50

GROUP BY dbid, loginame ,login_time

order by DB_name(dbid)

 

 

--Declare @maxversion bigint

 

--select top(1) @maxversion= [version] from  [Connection_details] order by [version] desc

 

--select SUM(convert(int,numberofconnections)) as numberofconnection

-- , [loginname]

--  from [Connection_details]

--where login_time between '2017-05-25 15:00:45.267' and '2017-05-25 16:00:45.267'

--and [version]=@maxversion

--group by [loginname]