How to Put All User Databases to Single User Mode - SQL Server Tutorial

How to Put All User Databases to Single User Mode

As a SQL Server DBA or developer, you are required to offer the scripts that can be used to single user mode all of the databases to a single SQL Server instance.

This article explains how to use Transact-SQL or SQL Server Management Studio to change a user-defined database in SQL Server to single user mode. Single-user mode, which is typically used for maintenance operations, stipulates that only one person at a time may access the database.

Using T-SQL Scripts:

USE master;

 GO

 ALTER DATABASE SampleDB

 SET SINGLE_USER

 WITH ROLLBACK IMMEDIATE;

 GO

 ALTER DATABASE SampleDB

 SET READ_ONLY;

 GO

 --You can also set the database to Multiple or Restricted access.

 ALTER DATABASE SampleDB

 SET MULTI_USER;

 GO

Use SQL Server Management Studio

To set a database to single-user mode:

1.     In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2.     Right-click the database to change, and then select Properties.

3.     In the Database Properties dialog box, select the Options page.

4.     From the Restrict Access option, select Single.

5.     If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, select Yes.

 

--To execute this query for multiple databases we can use Cursor, the select query in the cursor can be modified as per your requirement. 

USE MASTER

GO

 

DECLARE @DatabaseName AS VARCHAR(128)

 

DECLARE Cur CURSOR

FOR

--Get list of Database those we want to put into Single User Mode

SELECT NAME

FROM sys.databases

WHERE user_access_desc = 'Multi_USER'

    AND NAME NOT IN (

        'master'

        ,'tempdb'

        ,'model'

        ,'msdb'

        )

 

OPEN Cur

 

FETCH NEXT

FROM Cur

INTO @DatabaseName

 

WHILE @@FETCH_STATUS = 0

BEGIN

    --Innser  Cursor Start

    --Kill all connection to DB before Putting into Single User Mode

    DECLARE @Spid INT

 

    DECLARE KillProcessCur CURSOR

    FOR

    SELECT request_session_id

    FROM sys.dm_tran_locks

    WHERE resource_database_id = DB_ID(@DatabaseName)

 

    OPEN KillProcessCur

 

    FETCH NEXT

    FROM KillProcessCur

    INTO @Spid

 

    WHILE @@FETCH_STATUS = 0

    BEGIN

        DECLARE @SQL VARCHAR(500) = NULL

 

        SET @SQL = 'Kill ' + CAST(@Spid AS VARCHAR(5))

 

        EXEC (@SQL)

 

        PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5)) +

' killed successfull'

 

        FETCH NEXT

        FROM KillProcessCur

        INTO @Spid

    END

 

    CLOSE KillProcessCur

 

    DEALLOCATE KillProcessCur

 

    --Inner Cursor Ends

    --Outer Cursor: Put DB in single User Mode

    DECLARE @SQLSingleUSer NVARCHAR(MAX) = NULL

 

    SET @SQLSingleUSer = 'ALTER DATABASE [' + @DatabaseName + ']

 

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

 

    PRINT @SQLSingleUSer

 

    EXEC (@SQLSingleUSer)

 

    FETCH NEXT

    FROM Cur

    INTO @DatabaseName

END

 

CLOSE Cur

 

DEALLOCATE Cur

 

--Check if all DBS are in Single Mode

SELECT NAME AS DBName

    ,state_desc

    ,user_access_desc

FROM sys.databases

WHERE user_access_desc = 'SINGLE_USER'