Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012 Posts: 106 Points: 327 Location: Tbilisi
Thanks: 0 times Was thanked: 0 time(s) in 0 post(s)
|
BB კოდი:USE [MASTER] GO
SET NOCOUNT ON;
DECLARE @DatabaseOrServerWide CHAR(1) DECLARE @Cursor CURSOR DECLARE @dbid INT DECLARE @ProcID INT DECLARE @User VARCHAR(100) DECLARE @DatabaseName VARCHAR(200) DECLARE @Info VARCHAR(10) DECLARE @ProcessDetails TABLE ( ProcessID INT, LoginName VARCHAR(200) )
--Kill all server processes or just process for a specific database: SET @DatabaseOrServerWide = 'D' -- D = Database, S = Server --If database only, set database name: SET @DatabaseName = 'ApexERP'
--Get process details for specified database IF @DatabaseOrServerWide = 'D' BEGIN SELECT @dbid = [dbid] FROM sys.sysdatabases WHERE [name] = @DatabaseName
IF @dbid IS NULL BEGIN PRINT @DatabaseName + ' not found on current SQL instance.' GOTO EndProcess END
INSERT INTO @ProcessDetails SELECT MAX([spid]), [loginame] FROM sys.sysprocesses WHERE [dbid] = @dbid AND [loginame] != '' AND [loginame] != 'sa' AND [spid] != @@SPID GROUP BY [loginame]
IF (SELECT COUNT(0) FROM @ProcessDetails) = 0 BEGIN PRINT 'No users currently connected to ' + @DatabaseName + ' excluding sa processes and this thread.' GOTO EndProcess END END
--Get process details for entire instance IF @DatabaseOrServerWide = 'S' BEGIN
INSERT INTO @ProcessDetails SELECT MAX([spid]), [loginame] FROM sys.sysprocesses WHERE [loginame] != '' AND [loginame] != 'sa' AND [spid] != @@SPID GROUP BY [loginame]
IF (SELECT COUNT(0) FROM @ProcessDetails) = 0 BEGIN PRINT 'No users currently connected to instance, excluding sa processes and this thread.' GOTO EndProcess END END
--Kill processes SET @Cursor = CURSOR FOR SELECT ProcessID, LoginName FROM @ProcessDetails OPEN @Cursor FETCH NEXT FROM @Cursor INTO @ProcID, @User
WHILE (@@FETCH_STATUS = 0) BEGIN
EXEC('KILL ' + @ProcID) PRINT 'Process killed from login: ' + @User
FETCH NEXT FROM @Cursor INTO @ProcID, @User
END
CLOSE @Cursor DEALLOCATE @Cursor
--End information EndProcess:
SELECT @Info = COUNT(0) FROM @ProcessDetails
PRINT '' PRINT @Info + ' processes killed.' PRINT '' PRINT 'Script End'
|
|
Apex ltd. http://www.apex.ge
|