Solved

URGENT: force ALL DB connections to terminate!

Posted on 2004-08-10
2
432 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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