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…“) |
(kein Unterschied)
|
Version vom 23. Dezember 2015, 10:19 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 the Database name here !!!!!!!!!!!! Set @DBName = 'REPORT' 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 @spidstr EXEC(@spidstr) SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) END