Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: T-SQL to disconnect users from a db?

    Is there a way to forcibly disconnect users from a db with t-sql? I'm doing a nightly restore of a db and users who shouldnt remain connected after hours, are. So I cannot restore the db unless I disconnect them as the db cannot be obtained for exclusive access.

    Any thoughts on this?

    Edward Hunter, Data Application Engineer
    comScore Networks

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    put this in a userkill.sql or whatever filename, run it from an osql session:
    or
    compile it to a sproc.

    I do an -H SQLMAINTER from osql job so that job's user is not killed, as you can see downstairs.


    SET NOCOUNT ON
    DECLARE @strSQL varchar(255)

    PRINT 'Killing ' + UPPER(db_name()) + ' Database Connections'
    PRINT '----------------------------------------------------'
    DECLARE LoginCursor CURSOR READ_ONLY
    for select spid, loginame from master..sysprocesses
    where UPPER(cmd) not in (
    'LAZY WRITER',
    'LOG WRITER',
    'SIGNAL HANDLER',
    'LOCK MONITOR',
    'TASK MANAGER',
    'CHECKPOINT SLEEP')
    AND db_name(dbid) = db_name()
    AND hostname != 'SQLMAINTER'

    DECLARE @spid varchar(10), @loginame varchar(255)
    OPEN LoginCursor

    FETCH NEXT FROM LoginCursor INTO @spid, @loginame
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT 'Killing user spid: ' + @spid + ' Name: ' + @loginame
    SET @strSQL = 'KILL ' + @spid
    EXEC (@strSQL)
    END
    FETCH NEXT FROM LoginCursor INTO @spid, @loginame
    END
    CLOSE LoginCursor
    DEALLOCATE LoginCursor
    go

  3. #3
    Join Date
    Jan 2004
    Posts
    71

    ..

    Issue a command to call this:

    Declare @tblConnectedUsers Table (
    SPID int )

    Declare @vcSQLText varchar(200),
    @iSPID int

    --Get the currently connected users
    Insert into @tblConnectedUsers
    Select p.spid
    from master.dbo.sysprocesses p (nolock)
    join master..sysdatabases d (nolock) on p.dbid = d.dbid
    Where d.[name] = 'EBN_QA' --> database name here

    --Loop though the connected users and kill their connections
    While 1 = 1
    Begin

    Select top 1 @iSPID = SPID
    From @tblConnectedUsers
    Where SPID > IsNull(@iSPID, 0)
    order by SPID asc

    --Break when there are no more SPIDs
    If @@RowCount = 0
    Break

    --Build the SQL string
    Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID)

    Exec( @vcSQLText )

    End

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd probably use ALTER DATABASE, something like:
    Code:
    ALTER DATABASE
       SET RESTRICTED_USER
       ROLLBACK IMMEDIATE
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •