Solved

URGENT: force ALL DB connections to terminate!

Posted on 2004-08-10
2
428 Views
Last Modified: 2010-05-18
Our weekly backup failed because some joker was working at 3am Sunday morning and had a connection open to the DB! We have a BATch file which ...

isql -S MyOldSvrName -E -U MyLoginID -P MyPswd -Q "sp_detach_db 'MyDB', 'true'"
del \\MySvr\backup\MyDB_Data.MDF
del \\MySvr\backup\MyDB_Log.LDF
copy \\MySvr\MyDB_Data.MDF \\MySvr\backup\MyDB_Data.MDF
copy \\MySvr\MyDB_Log.LDF \\MySvr\backup\MyDB_Log.LDF
isql -S MySvr -E -U MyLoginID -P MyPswd -Q "sp_attach_db @dbname = 'MyDB', @filename1 = 'C:\MyDB_Data.MDF', @filename2 = 'C:\MyDB_Log.LDF'"

To run sp_detach_db ALL CONNECTIONS MUST BE TERMINATED!
How to force-terminate all connections using a stored proc?

Any ideas?
0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 14

Assisted Solution

by:adwiseman
adwiseman earned 500 total points
ID: 11766821
This came out of the back of a SQL server book somewhere.  i've used it all the time.  You also can make the db readonly, and then detach.  I'll try and find the post that had that code.


CREATE PROCEDURE usp_KillUsers
  @p_DBName SYSNAME = NULL
AS

/* Check Paramaters                    */
/* Check for a DB name                 */
IF (@p_DBName IS NULL)
BEGIN
  PRINT 'You must supply a DB Name'
  RETURN
END -- DB is NULL
IF (@p_DBName = 'master')
BEGIN
  PRINT 'You cannot run this process against the master database!'
  RETURN
END -- Master supplied
IF (@p_DBName = DB_NAME())
BEGIN
  PRINT 'You cannot run this process against your connections database!'
  RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables                   */
DECLARE @v_spid INT,
        @v_SQL  NVARCHAR(255)

/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
   FAST_FORWARD FOR
 SELECT spid
   FROM master..sysprocesses (NOLOCK)
  WHERE db_name(dbid) = @p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)
  BEGIN
    SELECT @v_SQL = 'KILL ' + CONVERT(NVARCHAR, @v_spid)
--    PRINT @v_SQL
    EXEC (@v_SQL)
  END -- -2
  FETCH NEXT FROM c_Users INTO @v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users
0
 
LVL 14

Accepted Solution

by:
adwiseman earned 500 total points
ID: 11766971
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20622065.html?query=&topics=42

From brett

In SQL 2000, you were given the ability to change properties on the database and kill all connections.....

This changes the database to readonly, killing all connections, and then changes the property back to read write.

alter database hwmf_db_idea set read_only with rollback immediate
go
alter database hwmf_db_idea set reqd_write with rollback immediate
go


So you could change the db to read-only.  Then detach to backup.  After reattaching, set properties bach to readwrite
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
sql server connection string in config file 4 41
Format Output of Select Statement 2 38
T-SQL: need to reset a declared variable 4 33
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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