Windows/MSSQL: Unterschied zwischen den Versionen
Aus SchnallIchNet
< Windows
Cbs (Diskussion | Beiträge) (Die Seite wurde neu angelegt: „== Transact SQL == snippets... === Kill all Processes === print and kill all connections to a particular DB <pre> USE master GO SET NOCOUNT ON DECLARE @D…“) |
Cbs (Diskussion | Beiträge) |
||
Zeile 19: | Zeile 19: | ||
SET @spidstr = '' | SET @spidstr = '' | ||
− | + | Set @DBName = 'PROD' | |
− | Set @DBName = ' | + | |
− | + | ||
IF db_id(@DBName) < 4 | IF db_id(@DBName) < 4 | ||
BEGIN | BEGIN | ||
Zeile 32: | Zeile 30: | ||
IF LEN(@spidstr) > 0 | IF LEN(@spidstr) > 0 | ||
BEGIN | BEGIN | ||
+ | PRINT '' | ||
PRINT @spidstr | PRINT @spidstr | ||
+ | PRINT '' | ||
EXEC(@spidstr) | EXEC(@spidstr) | ||
SELECT @ConnKilled = COUNT(1) | SELECT @ConnKilled = COUNT(1) | ||
FROM master..sysprocesses WHERE dbid=db_id(@DBName) | FROM master..sysprocesses WHERE dbid=db_id(@DBName) | ||
+ | PRINT 'Killed: '+convert(varchar, @ConnKilled)+' Connections' | ||
END | END | ||
</pre> | </pre> |
Version vom 23. Dezember 2015, 10:28 Uhr
Transact SQL
snippets...
Kill all Processes
print and kill all connections to a particular DB
USE master GO SET NOCOUNT ON DECLARE @DBName varchar(50) DECLARE @spidstr varchar(8000) DECLARE @ConnKilled smallint SET @ConnKilled=0 SET @spidstr = '' Set @DBName = 'PROD' IF db_id(@DBName) < 4 BEGIN PRINT 'Connections to system databases cannot be killed' RETURN END SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; ' FROM master..sysprocesses WHERE dbid=db_id(@DBName) and spid <> @@SPID IF LEN(@spidstr) > 0 BEGIN PRINT '' PRINT @spidstr PRINT '' EXEC(@spidstr) SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) PRINT 'Killed: '+convert(varchar, @ConnKilled)+' Connections' END