How to get exclusive access of a database and roll back all the pending transactions on that database.

Problem: How to get exclusive access of a database and roll back all the pending transactions on that database.

Solution: Set a Database to Single-user Mode

There are some cases where you want to take the database exclusive access for example when restoring the database from a backup you may want to stop the database to accept transactions as the log won’t be respond to incoming pending transactions.

This particular option would be useful in maintenance cases, where you have to stop the SQL Server access to users and lock the transactions.

Example:

In this example, I would like to show we can set a database to single user mode and roll back the pending transactions that are happening on the database.

USE MASTER

GO

ALTER DATABASE [DB]-- change it to the database you want to set the single user mode on

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

Results:

As you can see below database db is set to single user mode now.