How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)?
Things like number of currently open connections, and history of opened and closed connections, if these informations are stored somewhere. Who used that connection (login name, application, process id, anything)?
Background: there is a web app, and an Oracle linked server, and the Oracle dba says that our MS Sql server is spamming hundred of connections to their server. Currently our app is only accessible for a few users, and only doing simple selects from a view... So I can't really imagine that caused hundreds of connections.
Also what could cause excessive amounts of open connection form the MS Sql db to the Oracle db? Or what could keep the connection open for too long time? Can Sql Server Management Studio go wild, and open unnecessary connections?
One thing to add: We are using OpenQuery to query the Oracle db, so the Oracle db does the heavy lifting (selecting data from a pretty big view). The documentation says nothing about having to explicitly close the connection, is there any known issue regarding this?