Dependable query to determine who is connected to a database

I want to programmatically restore a database from backup.  But in order to do so, one must first break all existing connections to that database.  The following queries list NEARLY all connections:

select spid, rtrim(loginame) from sys.sysprocesses where db_name(dbid)='MyDatabaseName'
sp_who
sp_who2

Unfortunately, those queries do not always list every last connection.  Once in a while, those queries miss a connection.  After running one of the above queries, it is easy enough to kill all reported user connections.  Unfortunately, this process does not always report and kill every connection.  I need a query to show every last connection to a database so that I can kill those connections and then restore that database from backup.

Oddly enough, the following commands do not assist whatsoever:
alter database MyDatabaseName set offline with rollback immediate
alter database MyDatabaseName set online
alter database MyDatabaseName set single_user with rollback immediate
CFS_developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The OFFLINE command should work, although it can take some time before the db is actually offline.  Don't bring it back online, as that will allow other connections to be made:


ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

WHILE CAST(DATABASEPROPERTYEX('MyDatabaseName', 'Status') AS varchar(30)) <> 'OFFLINE'
BEGIN
    WAITFOR DELAY '00:00:02.000' --wait and then check again
END --WHILE

RESTORE DATABASE MyDatabaseName FROM ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFS_developerAuthor Commented:
Thanks for the suggestion.  Hopefully keeping the database offline will resolve the few restore failures I used to have.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.