Thursday, September 02, 2010

T-SQL: Disconnect Users from a Database

ניתן ל"נתק" משתמשים מבסיס נתונים של SQL Server באמצעות T-SQL:
USE master
go
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'DataBase Name'
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
      datediff(mi, @start, current_timestamp) < 5)
   begin
       DECLARE spids CURSOR FOR
              SELECT convert(varchar, spid) FROM sysprocesses
                      WHERE dbid = db_id(@dbname)
       OPEN spids
       while(1=1)
       BEGIN
              FETCH spids INTO @spid
              IF @@fetch_status < 0 BREAK
              exec('kill ' + @spid)
       END
       DEALLOCATE spids
end
IF NOT exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname))
       EXEC sp_dboption @dbname, offline, true
else
   begin
       PRINT 'The following processes are still using the database:'
       SELECT spid, cmd, status, last_batch, open_tran, program_name, hostname
       FROM sysprocesses
       WHERE dbid = db_id(@dbname)
Read more: Dudi Nissan's Blog
Tags: