SQL 2000: Disconnect All Users

October 13th, 2007
by psykoprogrammer

If you’ve ever had to do any kind of maintaince on your Microsoft SQL Server 2000 database, you may need this. This script will diconnect any open connections to the specified database (identified by the ‘DBNAME’). Please note that this will kick off any connected users. Special thanks to Kyle Murphy for this script.

  1. DECLARE @databaseName varchar(255)
  2. SELECT @databaseName = ‘DBNAME’
  3.  
  4. DECLARE curkillproc
  5. CURSOR FOR SELECT
  6.   spid,dbs.name AS dbname
  7. FROM
  8.   master..sysprocesses pro,
  9.   master..sysdatabases dbs
  10. WHERE
  11.   pro.dbid = dbs.dbid AND
  12.   dbs.name = @databaseName
  13. FOR READ ONLY
  14.  
  15. DECLARE @varspid AS integer
  16. DECLARE @vardbname AS varchar(256)
  17. DECLARE @numUsers AS integer
  18. SET @numUsers = 0
  19. OPEN curkillproc
  20. FETCH NEXT FROM
  21.   curkillproc
  22. INTO @varspid, @vardbname
  23. WHILE @@fetch_status = 0
  24. BEGIN
  25.   EXEC(‘kill ‘ + @varspid)
  26.   SET @numUsers = @numUsers + 1
  27.   FETCH NEXT FROM curkillproc
  28.   INTO @varspid, @vardbname
  29. END
  30. CLOSE curkillproc
  31. DEALLOCATE curkillproc
  32. SELECT @numUsers AS NumUsersDisconnected

Comments (0)

No comments yet

Leave a Reply

You must be logged in to post a comment.