There are a lot of different ways that you can check on the number of active connections in SQL Server.  You can use sp_who or sp_who2.  You can also read the performance counters.  However, none of these options worked for my specific scenario: I wanted to log the number of active connections to the database when our DAL was instantiated.  We keep having strange intermittent errors where our application appears to lose connection to the database server even though the connection is actually fine.  When the problem occurs, it only affects a single worker process on the host box; all other worker processes remain fine.  So, my theory is that we’re starving the connection pool, which probably means that somewhere in our 11,000 line DAL (go ahead, laugh, I’ll wait) is a connection leak.  Bad news. 

Anyway, to test my theory, like I said, I just wanted to log the number of active connections whenever someone an instance of the DAL is instantiated.  Since I don’t need details, I just need a count, sp_who and sp_who2 are right out.  They return a bunch of crap that I don’t care about.  Next up was this handy dandy statement that can be executed as a scalar:

   1: SELECT cntr_value FROM master..sysperfinfo as p 

   2: WHERE p.object_name = 'SQLServer:General Statistics' 

   3: And p.counter_name = 'User Connections'

Unfortunately, the database user that our DAL uses doesn’t have permission to query master..sysperfinfo.  I briefly considered altering permissions, but I decided instead that I’d just wrap it in a user defined function that executes as the database owner:

   1: create function [dbo].GetConnectionCount

   2: (

   3: )

   4: returns int

   5: with execute as owner

   6: as

   7: begin

   8:     return (SELECT cntr_value FROM master..sysperfinfo as p 

   9:             WHERE p.object_name = 'SQLServer:General Statistics' 

  10:             And p.counter_name = 'User Connections')

  11: end

And presto, I can now call this UDF to get and log the number of active connections.