Dependable query to determine who is connected to a database

Posted on 2014-08-11
Last Modified: 2014-08-12
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'

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
Question by:CFS_developer
    LVL 68

    Accepted Solution

    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:


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

    RESTORE DATABASE MyDatabaseName FROM ...

    Author Comment

    Thanks for the suggestion.  Hopefully keeping the database offline will resolve the few restore failures I used to have.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now