kick out users and put db in single user mode

• sifuhall

  Old Hand

  Points: 324

  More actions

  March 21, 2005 at 8:46 pm

  #68801

  Occasionally I have had quite a bit of trouble when I try to put a db in single user mode.  (Users stay logged in, various apps that should have been shut down are left on, etc.)

  I know it would be much easier if I could trust everyone to be out of the db when they are supposed to and all apps will be logged out, but frequently my window of opportunity for a given db may be very early in the a.m. and I do not feel like tracking down each user or app.

  I found a script online that kicks all users out of a db and it seems to work pretty well, but sometimes apps will grab another connection before I can put the db in single user mode and grab the db.

  I tried to modify it so it immediately puts the db in single user mode, then grabs the db but it doesn't work.  After executing it bombs on the line putting the db in single user mode because all connections to the db are not closed.

  Can anyone offer any insight into this?  How can I kick out all users of a given db, place it in single user mode, and grab the db before anyone else gets it?

   

  Here is the code I'm trying now (that doesn't work):

   

  --Declare @tblConnectedUsers Table (

  -- SPID  int  &nbsp

  Declare @vcSQLText  varchar(200),

   @iSPID   int

  --Get the currently connected users

  --Insert into  @tblConnectedUsers

  Select p.spid

  into #tblConnectedusers

  from master.dbo.sysprocesses p (nolock)

  join master..sysdatabases d (nolock) on p.dbid = d.dbid

  Where d.[name] = 'SOH' --> 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

  drop table #tblConnectedusers

  go

  use pdp_archive

  go

  EXEC sp_dboption 'pdp', 'single user', 'TRUE'

  go

  use pdp

  go

• Helen-84411

  Hall of Fame

  Points: 3320

  More actions

  March 21, 2005 at 9:20 pm

  #548115

  Hi,

  You can also disconnect any users who have open connections to the server by using the ROLLBACK IMMEDIATE command. You can't use Enterprise Manager with this command; you must use Query Analyzer to execute the following query:

  ALTER DATABASE <db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  After you execute this command, SQL Server drops all connections and rolls back their transactions immediately. All users performing a transaction will receive a connection error and will be unable to reconnect to the database.

  You can optionally specify a certain amount of time that SQL Server will give users to complete their transactions before it disconnects them. You use the ROLLBACK AFTER keywords as the following query shows:

  ALTER DATABASE <db> SET SINGLE_USER WITH ROLLBACK AFTER 20 SECONDS

  Hope this helps.

  
  Helen
  --------------------------------
  Are you a born again. He is Jehova Jirah unto me