CODEWITHSUNDEEP
sqlsql-server
user9483506
user9483506

Reputation:

SET Single_USER Mode SQL Server

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

Upvotes: 0

Views: 8144

Answers (1)

CR241
CR241

Reputation: 2603

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

Upvotes: 1

Related Questions