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!