Kill all the existing user connections in SQL Server

How Kill all the existing user connections in SQL Server

From what SPID, user connections will start ?

Answer is 51.

SPID < 51 are defined as "System Connections"

If we want to perform any operation like Restore db/Move db/ Change file Names/ Setting any Major properties which needs to kill all existing connections, how should be we proceed ?

Fastest way to kill the user sessions is:

Use dbname
Go

Alter database dbname
set single_user

/* Above query will execute successfully when no other user connections are made on the database */

/* What if other connections are made ? What happens in SQL Server at that time ? */

When other connections are established, SQL Server will start the transaction for putting the database in single user mode, but will not proceed for next opearation and will be running state.

After executing the command to put the db in single user mode, i have check the status of the SPID and whats going in the LDF file.

As shown in the screenshot,

Status of SPID 57 is running.

When checked in the LDF, it is not proceding further the SPID 57 raised Transaction ID.

Operation is halted after "LOP_BEGIN_XACT".
This will proceed only when i kill the session which is blocking its way.

SPID 57 is getting blocked with SPID 58.
Am gonna kill it now.

Kill 58
Go

Once the session is killed, we can see that DB will be put into single user mode. Below is what happened in LDF file.

Operation proceeded further after killing the session 58 and we can see operation "LOP_COMMIT_XACT" with END TIME of the transaction ID.

If we want to go for killing all the sessions at time:

Alter database dbname
set single_user with rollback immediate
Go

/* Above command will put the db in single user mode by killing all the connection explicityly */

Put the db back in multi user mode

Alter database dbname
set multi_user with rollback immediate
Go

Hope the above information helps

Thanks