Monitor concurrent session in MS SQL Server 2008

Question

Dear Experts,

I have a requirement to get the no of connections in ms sql server (2008 R2). I there any way to get the connections history for the whole day or past time as well.

Thanks

UsafUnas

Answer 1

Dear UsafUnas,

Try this:

SELECT  P.hostname ,
        P.program_name ,
        P.loginame ,
        DB_NAME(P.dbid)
FROM    sys.sysprocesses P
WHERE   P.dbid > 0;
SELECT  COUNT(*)
FROM    sys.sysprocesses P
WHERE   P.dbid > 0

Regards

Saeid

---

http://sqldevelop.wordpress.com/

Answer 2

See this

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/08/16/703079.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 3

Dear Saeid,

Thanks for the udpate. These view gives me run time information. Is there a way to keep the history of the sessions. Like If how can I calculate the number of connection get connected with MS SQL instance for the last 24 hours.

Regards,

UsafUnas

Answer 4

Hello UsafUnas,

There are no such history information available, you have to implement an own solution to log these information, e.g. with a schedule SQL Server Agent to write timestamp + count of connection into an own logging table.

---

Olaf Helper

Blog Xing

Answer 5

Thanks for the udpate. These view gives me run time information. Is there a way to keep the history of the sessions. Like If how can I calculate the number of connection get connected with MS SQL instance for the last 24 hours.

There is no such history enabled by default. However, you could set up a trace to capture login and logout events. With such a trace you need to decide how you handle connection pooling. When a connection is reused, do you want to count that as a new connection or not?

---

Erland Sommarskog, SQL Server MVP, [e-mail]