Finding concurrent connections with machine names in SQL SERVER

Question

How to know concurrent number of users in SQL Server 2008/2012/2014 with client machine names who are connected to a db. Is there a way in Sql Server to know at what time were the most users logged in/jobs running in last 7 days. Thanks in advance.

Answer 1

SQL Server does not save this information automatically, so you would need to set something up. A simple-minded approach would be a job that saved selected columns in sys.dm_exec_sessions to a table every minute or so. Be sure to filter out system processes.

Keep in mind that the host name is set in the connection string and may not be accurate.

Answer 2

Hi @DM

Unless you set up logging or trace you cannot retrieve that information natively.

Using SQL Server Management Studio

Configure Login Auditing (SQL Server Management Studio) Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. See this blog how to read from the error log.

Using Extended Event Trace

Audit Login Event Class

The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.

Refer to this blog for more details: Tracking Logins with Extended Events

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.