Active Database Connections SQL Express, how many

How can I determine if everyone has logged off of the SQL Express database, ie, no one else is connected.

I need to clean up some files that are generated in my code, but I only want to do that when everyone has logged off the database?

In MS Access, I could try to re-open the MDB file exclusively.

Is there a connection number etc?

Hi there,

There isn't a connection number per se (well there is the @.@.CONNECTIONS function but that returns all server connections since the server was last started/restarted so it's not really useful for your situation).

You can use the stored procedure sp_who or even sp_who2 by opening up a new query window and typing in "exec sp_who" or "exec sp_who2".

These stored procedures will give you a result set showing all active processes in the database, what database they are currently working in and also what machine (host) they are connected from. Note that a connection may take up multiple processes so you are looking for distinct process IDs in the output from these stored procedures.

I guess you could use the output from the stored procs to see that you're the only person connected to the database (use the combination of the host name, process ID and database name to determine this).

You could be more complicated and actually query system tables for the information you want, but using the stored procedure should be sufficient.

Also, you can set your database to single user to prevent anyone else connecting when you do your work....Actually, from my understanding, setting a database to single user mode actually boot anybody who is currently connected but not you if anyone actually is connected when you set the database to single user mode.

To set single user mode, you can:

1) Right click on your database in Management Studio Express and select "Properties" from the context menu that appears

2) In the "Database Properties" dialog, there will be an option (on the left hand side) for "Options". Click on this

3) There will be a set of properties displayed in the bottom half of the dialog. Scroll through these and there should be a property called "Restrict Access". Change this property (should currently be MULTI_USER) to SINGLE_USER and apply.

4) After you've finished, set the "Restrict Access" property back to MULTI_USER so that other people can connect. Note that sometimes Management Studio Express can have a hissy fit when you try to do this....In that case, close Management Studio Express, re-open it and re-connect to the server and then try again.

NB (1). It might take a little bit of time for all people using your database to be booted once you set the access to SINGLE_USER so wait a bit before doing any work.

NB (2). If you are connecting as an account with SysAdmin privileges (e.g. "sa") and everyone else who connects does not use an account with either SysAdmin, dbowner or dbcreator privileges then you can also set the access to RESTRICTED_USER instead of SINGLE_USER

NB (3). Restricting database access can also be done through the ALTER DATABASE statement if you want to write some SQL for it. I find the GUI is easier, though, but if you want to use SQL then look in SQL Server Books Online for info on the ALTER DATABASE statement.

Hope that helps a bit, but sorry if it doesn't
|||

If you're using a tool such as Management Studio (Full or Express) you can open the Activity Monitor from the Management node. This will tell you all the processes that are open and what they are doing. You can also find information about existing Locks.

If these suggestions don't meet your needs, you could also ask this question on the SQL Database Engine forum or the T-SQL forum. Those forums are great places to ask general questions about how to do general things in SQL Server.

Regards,

Mike

Mark the best posts as Answers!

Active connections

Does anybody know an easy way to check how many connections are being made to the database? I'm getting the following error

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: A connection pooling error has occurred.

and find it hard to believe that there are no connections left in the pool.

I've checked in Enterprise Manager and the maxmimum number of process running under the user account used to access the DB from code is 2. Strangely the error seems to occur when I explicitly close the connection.

thanks
SimonHi Simon,

You could run sp_monitor from Query Anylizer to find out.

EXEC sp_monitor

It will tell you how many connections since the last start and how many are present.

:)

JB|||Make sure you're closing all of you connections on all of your pages. Here are a few perf counters to look at:
\\bob\.NET CLR Data(_global_)\SqlClient: Current # connection pools
\\bob\.NET CLR Data(_global_)\SqlClient: Current # pooled and nonpooled connections
\\bob\.NET CLR Data(_global_)\SqlClient: Current # pooled connections|||OK, looks like something weird to me.

When I run in debug mode my number of pooled connections:

1. Goes up by 4 when clean starting ... i.e. Application_Start fires.
2. Goes up by 2 when normal start.
3. Does not go up when navigating from page to page.

When I run in 'normal' mode the number of pooled connections:

1. Goes up by 2 when clean starting ... i.e. Application_Start fires.
2. Does not go up when normal start.
3. Does not go up when navigating from page to page.

It never seems to decrease though.

What is going on??

Simon|||pooled connections will not be terminated from the database server's perspective until the connection inactivity timeout is reached on the pooled connections.

So under normal circumstances, you wont see the number of connections decrease unless your app sits still for a while.

Active Connections

Hello,
I got an message in MOM the I have more than 500 concurrent open connections on my sql server. So I checked this and saw that the number is about 200000. Nevertheless I can see only 80 connections in the activity monitor and the corresponding tables in the msdb.
So I restarted the server (it's a test server) and after an hour there already constantly 3000 user connections in the performance monitor and only 80 in the activity monitor.
Does anyone know how I can check what the other connections are or which process these connections create ?
Manfred
Did you compare:
EXEC sp_who2;
and
EXEC sp_who2 'active';
Do you have an application that perhaps does not release connections?
"Manfred Schopf" <manfred.schopf@.aon.at> wrote in message
news:newscache$vz4o8j$mg7$1@.news.sil.at...
> Hello,
> I got an message in MOM the I have more than 500 concurrent open
> connections on my sql server. So I checked this and saw that the number is
> about 200000. Nevertheless I can see only 80 connections in the activity
> monitor and the corresponding tables in the msdb.
> So I restarted the server (it's a test server) and after an hour there
> already constantly 3000 user connections in the performance monitor and
> only 80 in the activity monitor.
> Does anyone know how I can check what the other connections are or which
> process these connections create ?
> Manfred
|||Manfred Schopf wrote:
> Hello,
> I got an message in MOM the I have more than 500 concurrent open connections on my sql server. So I checked this and saw that the number is about 200000. Nevertheless I can see only 80 connections in the activity monitor and the corresponding tables in the msdb.
> So I restarted the server (it's a test server) and after an hour there already constantly 3000 user connections in the performance monitor and only 80 in the activity monitor.
> Does anyone know how I can check what the other connections are or which process these connections create ?
> Manfred
What counter are you monitoring in Performance Monitor? If you query
the sysprocesses table, you'll see exactly how many connections there are:
SELECT * FROM master.dbo.sysprocesses
Each row is a connection
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Active Connections

Hello,
I got an message in MOM the I have more than 500 concurrent open connections
on my sql server. So I checked this and saw that the number is about 200000
. Nevertheless I can see only 80 connections in the activity monitor and the
corresponding tables in th
e msdb.
So I restarted the server (it's a test server) and after an hour there alrea
dy constantly 3000 user connections in the performance monitor and only 80 i
n the activity monitor.
Does anyone know how I can check what the other connections are or which pro
cess these connections create ?
ManfredDid you compare:
EXEC sp_who2;
and
EXEC sp_who2 'active';
Do you have an application that perhaps does not release connections?
"Manfred Schopf" <manfred.schopf@.aon.at> wrote in message
news:newscache$vz4o8j$mg7$1@.news.sil.at...
> Hello,
> I got an message in MOM the I have more than 500 concurrent open
> connections on my sql server. So I checked this and saw that the number is
> about 200000. Nevertheless I can see only 80 connections in the activity
> monitor and the corresponding tables in the msdb.
> So I restarted the server (it's a test server) and after an hour there
> already constantly 3000 user connections in the performance monitor and
> only 80 in the activity monitor.
> Does anyone know how I can check what the other connections are or which
> process these connections create ?
> Manfred|||Manfred Schopf wrote:
> Hello,
> I got an message in MOM the I have more than 500 concurrent open connections on my
sql server. So I checked this and saw that the number is about 200000. Nevertheless
I can see only 80 connections in the activity monitor and the corresponding tables
in
the msdb.
> So I restarted the server (it's a test server) and after an hour there alr
eady constantly 3000 user connections in the performance monitor and only 80
in the activity monitor.
> Does anyone know how I can check what the other connections are or which p
rocess these connections create ?
> Manfred
What counter are you monitoring in Performance Monitor? If you query
the sysprocesses table, you'll see exactly how many connections there are:
SELECT * FROM master.dbo.sysprocesses
Each row is a connection
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think there might be a leaky application. I want to find out which one
this could be.
sp_who2 - 60 rows and with 'active' - 24 rows.
the perfmon counter tells me that I have about 44 logical connections
and 3000 user connections.
Manfred
Aaron Bertrand [SQL Server MVP] schrieb:
> Did you compare:
> EXEC sp_who2;
> and
> EXEC sp_who2 'active';
> Do you have an application that perhaps does not release connections?
>
>
> "Manfred Schopf" <manfred.schopf@.aon.at> wrote in message
> news:newscache$vz4o8j$mg7$1@.news.sil.at...
>|||Tracy McKibben schrieb:
> Manfred Schopf wrote:
> What counter are you monitoring in Performance Monitor? If you query
> the sysprocesses table, you'll see exactly how many connections there are:
> SELECT * FROM master.dbo.sysprocesses
> Each row is a connection
>
>
I got 64 rows.
I am monitoring 4 values in the perfmon:
user connections - 3089
logical connections - about 40
logins/sec, logouts/sec - I have much more logins than logouts per
seconds (about twice as much)
Manfred

Active Connections

Hello,
I got an message in MOM the I have more than 500 concurrent open connections on my sql server. So I checked this and saw that the number is about 200000. Nevertheless I can see only 80 connections in the activity monitor and the corresponding tables in the msdb.
So I restarted the server (it's a test server) and after an hour there already constantly 3000 user connections in the performance monitor and only 80 in the activity monitor.
Does anyone know how I can check what the other connections are or which process these connections create ?
ManfredDid you compare:
EXEC sp_who2;
and
EXEC sp_who2 'active';
Do you have an application that perhaps does not release connections?
"Manfred Schopf" <manfred.schopf@.aon.at> wrote in message
news:newscache$vz4o8j$mg7$1@.news.sil.at...
> Hello,
> I got an message in MOM the I have more than 500 concurrent open
> connections on my sql server. So I checked this and saw that the number is
> about 200000. Nevertheless I can see only 80 connections in the activity
> monitor and the corresponding tables in the msdb.
> So I restarted the server (it's a test server) and after an hour there
> already constantly 3000 user connections in the performance monitor and
> only 80 in the activity monitor.
> Does anyone know how I can check what the other connections are or which
> process these connections create ?
> Manfred|||Manfred Schopf wrote:
> Hello,
> I got an message in MOM the I have more than 500 concurrent open connections on my sql server. So I checked this and saw that the number is about 200000. Nevertheless I can see only 80 connections in the activity monitor and the corresponding tables in the msdb.
> So I restarted the server (it's a test server) and after an hour there already constantly 3000 user connections in the performance monitor and only 80 in the activity monitor.
> Does anyone know how I can check what the other connections are or which process these connections create ?
> Manfred
What counter are you monitoring in Performance Monitor? If you query
the sysprocesses table, you'll see exactly how many connections there are:
SELECT * FROM master.dbo.sysprocesses
Each row is a connection
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think there might be a leaky application. I want to find out which one
this could be.
sp_who2 - 60 rows and with 'active' - 24 rows.
the perfmon counter tells me that I have about 44 logical connections
and 3000 user connections.
Manfred
Aaron Bertrand [SQL Server MVP] schrieb:
> Did you compare:
> EXEC sp_who2;
> and
> EXEC sp_who2 'active';
> Do you have an application that perhaps does not release connections?
>
>
> "Manfred Schopf" <manfred.schopf@.aon.at> wrote in message
> news:newscache$vz4o8j$mg7$1@.news.sil.at...
>> Hello,
>> I got an message in MOM the I have more than 500 concurrent open
>> connections on my sql server. So I checked this and saw that the number is
>> about 200000. Nevertheless I can see only 80 connections in the activity
>> monitor and the corresponding tables in the msdb.
>> So I restarted the server (it's a test server) and after an hour there
>> already constantly 3000 user connections in the performance monitor and
>> only 80 in the activity monitor.
>> Does anyone know how I can check what the other connections are or which
>> process these connections create ?
>> Manfred
>|||Tracy McKibben schrieb:
> Manfred Schopf wrote:
>> Hello,
>> I got an message in MOM the I have more than 500 concurrent open
>> connections on my sql server. So I checked this and saw that the
>> number is about 200000. Nevertheless I can see only 80 connections in
>> the activity monitor and the corresponding tables in the msdb.
>> So I restarted the server (it's a test server) and after an hour there
>> already constantly 3000 user connections in the performance monitor
>> and only 80 in the activity monitor.
>> Does anyone know how I can check what the other connections are or
>> which process these connections create ?
>> Manfred
> What counter are you monitoring in Performance Monitor? If you query
> the sysprocesses table, you'll see exactly how many connections there are:
> SELECT * FROM master.dbo.sysprocesses
> Each row is a connection
>
>
I got 64 rows.
I am monitoring 4 values in the perfmon:
user connections - 3089
logical connections - about 40
logins/sec, logouts/sec - I have much more logins than logouts per
seconds (about twice as much)
Manfred

Actiohailkve User Connections

Hi:
How can I quickly find out active user connections to the database and the
Server?
ThanksExactly what is it that you want to find out about them?
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:3C36C435-2228-4C82-9B37-5D60E2464718@.microsoft.com...
> Hi:
> How can I quickly find out active user connections to the database and the
> Server?
> Thanks

Actiohailkve User Connections

Hi:
How can I quickly find out active user connections to the database and the
Server?
ThanksExactly what is it that you want to find out about them?
--
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:3C36C435-2228-4C82-9B37-5D60E2464718@.microsoft.com...
> Hi:
> How can I quickly find out active user connections to the database and the
> Server?
> Thanks

Actiohailkve User Connections

Hi:
How can I quickly find out active user connections to the database and the
Server?
Thanks
Exactly what is it that you want to find out about them?
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:3C36C435-2228-4C82-9B37-5D60E2464718@.microsoft.com...
> Hi:
> How can I quickly find out active user connections to the database and the
> Server?
> Thanks

Accessing mirrored databases in SqlServer 2005

Using the new sqlserver 2005 mirroring, can client connections be made to the non-principal database for, say, reporting purposes? Does this affect the failover?

Thanks

For reporting only run with safety off and no witness. Either one is enough to disable auto failover, but quorum (2 of 3) is required to run with safety on so with no witness both systems must be up to update the principal server. Safety off allows running with the mirror down and no witness. To use the mirror as a reporting server you can create database snapshots on an active mirror. This allows point in time read only access to the database.

http://msdn2.microsoft.com/en-us/library/ms175511.aspx