[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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
0
CFS_developer
Asked:
CFS_developer
1 Solution
 
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
 
CFS_developerAuthor Commented:
Thanks for the suggestion.  Hopefully keeping the database offline will resolve the few restore failures I used to have.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now