Post reply

transact job to kill all connections to a database

  • June 25, 2007 at 4:48 pm

    #69831

    I have a need to backup database 1 and restore it over top database 2. However, once in a while there are connections to database 2. How do I script a kill command for those connections ONLY to that database via a script ?

  • June 25, 2007 at 5:19 pm

    #715063

    I'd do something like this if you really don't care about killing all those connections:

    BACKUP DATABASE mydb TO....

    GO

    ALTER DATABASE mydb2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    GO

    RESTORE DATABASE mydb2 FROM...

  • June 25, 2007 at 5:20 pm

    #715064

    Oh, and at the end, do an ALTER DATABASE mydb2 SET MULTI_USER...

  • June 25, 2007 at 10:55 pm

    #715103

    You can use the procedure in the link given below to do the same.

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=271

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 26, 2007 at 1:28 am

    #715126

    Hi,

    I have this problem as well because we chuck all the users off at the end of financial period so we can run our reports (yes I know we could copy the database and run reports from that, but some concepts don't travel up the management hierarchy that well )

    Anyhow, I found this neat little script somewhere on this forum a while ago, apologies for plagiarising if you recognise it . It relies on having a program name in sysprocesses, it kills all spids connected using that program name, but without using a cursor, i have commented out the line that does the deed so you can check it out first...

    DECLARE @spidstr varchar(8000)

    SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '

    FROM master..sysprocesses WHERE program_name='YourApp.exe'

    -- EXEC(@spidstr)

    SELECT @spidstr

    David

    If it ain't broke, don't fix it...

  • June 26, 2007 at 1:56 pm

    #715331

    My little trick is to select detach database in the GUI. It will tell you there are active connections and do you want to kill them ?  I say yes, kill the connections, then cancel out of the detach screen.

  • June 26, 2007 at 2:32 pm

    #715340

    This is the best way to kick everyone, including administrators, out of the database and prevent them from reconnecting.

    use master
alter database MyDatabase set offline with rollback immediate

     

  • June 27, 2007 at 12:42 am

    #715437

    Don't remember where I found this but here you go. Put in your master DB and rock and roll:

    ----------------------------------------------------------------------------

    Create  PROCEDURE sp_KillAllProcesses 
    @DBNAME varchar (100)=NULL, /*database where we will kill processes. 
    If NULL-we will attempt to kill processes in all DBs*/
    @USERNAME varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists, 
    whose processes we are going to kill. If NULL-kill all processes. */

/*Purpose: Kills all processes in a given database and/or belonging to a specified user. 
If no parameters supplied it will attempt to kill all user processes on the server.
Server: all
Database: DBAservice
Created: Igor Raytsin,Yul Wasserman 2000-10-13 
Last modified: Yul Wasserman 2002-02-08 
*/ 
AS
SET NOCOUNT ON

DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @database_name varchar(100) 
DECLARE @exec_str varchar (255)  
DECLARE @error_str varchar (255)  

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@DBNAME)) or @DBNAME is NULL)
BEGIN
Set @error_str='No database '+ltrim(rtrim(@DBNAME)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)

If   @USERNAME is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR  
SELECT dbid,name  FROM master.dbo.sysdatabases where name=ltrim(rtrim(@DBNAME)) or @DBNAME is NULL
OPEN curDbUsers   
FETCH NEXT FROM curDbUsers INTO @dbid,@database_name  
WHILE @@FETCH_STATUS = 0   
BEGIN  
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@database_name+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@USERNAME))+'"' 
EXEC (@exec_str)  
 FETCH NEXT FROM curDbUsers INTO @dbid,@database_name   
END  
CLOSE curDbUsers  
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@USERNAME)) +' found.'
DROP TABLE ##DbUsers  
Raiserror(@error_str, 16,1)
RETURN-1
END
END 

ELSE --IF  @USERNAME is null
BEGIN
 INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@DBNAME))),-911),-911
END

--select * from ##dbUsers

DECLARE curAllProc CURSOR FOR  
SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp   
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc   
FETCH NEXT FROM curAllProc INTO @p_id, @dbid 
  
WHILE @@FETCH_STATUS = 0   
BEGIN    
 SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'  
 SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)   
 RAISERROR (@error_str,10,1)with log  
 EXEC (@exec_str)  
 FETCH NEXT FROM curAllProc INTO @p_id, @dbid 
END  
CLOSE curAllProc  
DEALLOCATE curAllProc
DROP TABLE ##DbUsers 
SET NOCOUNT OFF

    G. Milner

  • June 28, 2007 at 8:50 am

    #715882

    1) Anything that loops to kill spids should have an 'escape clause' to avoid getting stuck in an 'infinite' loop.  Some spids can take a LONG time to rollback.  Not sure about newer versions of SQL Server, but older ones could actually have spids that would NOT DIE too! 

    2) Not sure if single-user with rollback immediate makes sure YOU get the only connection to the db or if someone else can snatch it up before you can execute the restore.  From BOL, however, there are these additional concerns:

    Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

    1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
    2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.
    3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply