Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

URGENT: force ALL DB connections to terminate!

Posted on 2004-08-10
2
Medium Priority
?
435 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 2000 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

715 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