Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BartMan
Starting Member
22 Posts |
 Posted - 2003-12-04 : 17:12:47
|
Greetings, I was wondering if there was a built in way to list the current connection count to the sql server using transact-sql.I was able to list attempted, and current combined by using the following:SELECT GETDATE() AS 'Today''s Date and Time', @@CONNECTIONS AS 'Login Attempts' But I am not sure how to just list the current number of connections using transact sql? For in this case I don't care about the attempted connections.Thanks in advance for any suggestions!  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 17:16:31
|
| Do you mean:?SELECT COUNT(*) FROM master.dbo.sysprocessesThe above will include system processes though.Tara |
 |
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-12-04 : 17:30:27
|
| Thanks Tara, I didn't know that the connections to the sql server were stored in the sysprocesses table. That is interesting... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 17:32:11
|
| If you are going to use sysprocesses then you will need to add a WHERE clause so that you don't get system processes plus connections to other databases or other connections outside of your application. If your application has the application name in the connection string, then program_name column would be good to use for the WHERE clause. I wouldn't use the application userid as someone could be connecting through Query Analyzer with it or something like that.Tara |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-12-04 : 17:38:06
|
| That is a very cool idea! Thanks for the help! |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-04 : 18:48:42
|
| Just to add - On a multi-processor system, a spid using parallelism will have more than one row in sysprocesses. So, you want to get a distinct count of spids (> 50).You could also make use of a SQL Server performance monitor counter called 'User Connections'.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-05 : 07:22:10
|
| where ecid = 0 will get round the parallelism problem.Have a look athttp://www.nigelrivett.net/sp_nrSpidByStatus.htmlIt gives the connnections and the last command executed and any blocking.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
List current connection count on system using sql?