How to view active sessions in SQL Server instance databases

Run the following T-SQL script for listing all active sessions in all SQL Server databases of the open SQL instance.

SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame
 FROM    sys.sysprocesses
 GROUP BY dbid, loginame
 ORDER BY DB_NAME(dbid)

This will return a list of the active sessions, grouped by the number of active connections and login name and ordered by the name of the database.

Alternatively you can run the sp_who stored procedure by executing the following command as a script inside SQL Server Management Studio:

exec sp_who

Also you can open the Activity Monitor tool by right clicking on the SQL instance inside the SQL Server Management Studio and choose to launch the Activity Monitor. This shows a list of parameters of the active SQL instance and databases, such as running processes and system resources consumed.