Solved

URGENT: force ALL DB connections to terminate!

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

777 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