Different ways to Kill Active Connections in Database


There are multiple ways to kill all active connections in the current or specific database. To kill all the connections to your database, you can use the ALTER DATABASE command or you can loop through the system process and kill them.

One way is…

DECLARE @p_SPID int, @p_SQL nvarchar(2000),@dbName nvarchar(100)
SET @dbName = DB_NAME()
DECLARE #cur_Processes CURSOR FOR SELECT p.SPID FROM master.dbo.sysprocesses AS p JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid ) WHERE d.Name = @dbName AND p.SPID > 50 -- AND spid >= 51 (because spids of 50 or less are reserved for internal use.) 
OPEN #cur_Processes 
FETCH NEXT FROM #cur_Processes INTO @p_SPID 
WHILE @@FETCH_STATUS = 0 BEGIN
IF @@SPID != @p_SPID BEGIN

SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID ) PRINT @p_SQL EXECUTE( @p_SQL ) END

FETCH NEXT FROM #cur_Processes INTO @p_SPID 
END
CLOSE #cur_Processes 
DEALLOCATE #cur_Processes
GO

and another way is…


ALTER DATABASE AdventureWorksLT2008 SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO
ALTER DATABASE AdventureWorksLT2008 SET MULTI_USER
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s