SET Single_USER Mode SQL Server

Question

I need help with setting a database in Single User Mode, Every time i run below code, But it's taking endless process

USE {Database_Name}

ALTER DATABASE {Database_Name} SET SINGLE_USER;
 GO
DBCC CHECKDB({Database_Name},REPAIR_REBUILD)
 GO
ALTER DATABASE {Database_Name} SET MULTI_USER;
 GO

Do you mean the checkdb is taking a long time, or just setting the database to single user is taking ages? (or on setting it, you lose your connection and havn't seen that) — Andrew
Single user mode only taking time, after that now i set single user mode via property, then its working fine. — user9483506
SET SINGLE_USER WITH ROLLBACK IMMEDIATE cancels all running transactions and closes open connections. This tends to speed things up -- at the obvious cost of interrupting whatever else was going on . — Jeroen Mostert

CR241 CR241 · Accepted Answer · 2018-08-16T19:15:17

Below SQL set the database to SINGLE_USER mode to obtain exclusive access. Initially It sets database to READ_ONLY and returns access to the database to all users.

The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to the database to be immediately disconnected.

Example SQL:

USE master
GO

ALTER DATABASE DB_Name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB('database_name', REPAIR_REBUILD)
GO

ALTER DATABASE DB_Name
SET READ_ONLY;
GO

If you want to set Database back to Multi_suer, use below SQL:

ALTER DATABASE DB_name
SET MULTI_USER;
GO

For more details check this link: How to set a Database to Single-user Mode

SET Single_USER Mode SQL Server

1 Answers