Go Premium for a chance to win a PS4. Enter to Win

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

URGENT: force ALL DB connections to terminate!

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
volking
Asked:
volking
  • 2
2 Solutions
 
adwisemanCommented:
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
 
adwisemanCommented:
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

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!

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