Check no of open connection in SQL server

--> To allow users to view current activity on the database:
sp_who2

--> To give you the total number of connections per database on a database server:

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

--> To get the dbid from database name
SELECT DB_ID('MyDBName') as [Database ID]

--> To give you the process Ids of existing connections in the database (not necessarily open but existing):
SELECT spid
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid = (SELECT DB_ID('MyDBName') as [Database ID])

--> To give you information about the actual process id (replace 1018 with the spid):
dbcc inputbuffer (1018)

--> To kill a process
kill 1018