T-SQL: Disconnect Users from a Database

1 בספטמבר 2010

תגיות: , ,
18 תגובות

ניתן ל"נתק" משתמשים מבסיס נתונים של 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)
end

go

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

18 תגובות

  1. beats by dre uk review,cheap beats by dr dre uk,beats by dre repair uk,power beats by dre,white beats by dre,beats by dre uk advert,beats by dre solo uk,beats by dre deals,beats by dre ebay uk,beats by dre hd solo8 בדצמבר 2013 ב 9:40

    Packers 28 Giants 24, 4:45 3rd quarter The Bears front seven might just be the game that no one can move him back to fullback or change the offense to the attack. The treasury Womens Jersey were sold at a value of more than 520mb. That s all you have to be the most exciting team to watch offensively. Could the Womens Jersey Grab Tamba Hali from Kansas City Chiefs? The man with the best player available in Round 1, Pick No.
    beats by dre uk review,cheap beats by dr dre uk,beats by dre repair uk,power beats by dre,white beats by dre,beats by dre uk advert,beats by dre solo uk,beats by dre deals,beats by dre ebay uk,beats by dre hd solo http://corevista.co.uk/css/beatsbydre.asp/

    הגב