Hey guys,
Goodnight.

Today I come to bring a quick tip for those who wanted to do a quick restore, an alter database or any command that requires an exclusive lock on a database, but there were users executing queries on the database, and then you receive an error message like this SQL Server:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In this case, we must ensure that all existing connections that use this database are disconnected and then try to execute our command again. The best way to do this is by contacting users and aligning their needs so that they close their connections and finish executing their queries.

It is never recommended to KILL connections without users' consent, unless it is something critical or scheduled maintenance that had already been scheduled previously.

Deleting connections from a database via KILL

If it is really necessary to manually eliminate connections that are using a database, you can use this small snippet of T-SQL code to perform this task:

DECLARE @query VARCHAR(MAX) = ''

SELECT
    @query = COALESCE(@query, ',') + 'KILL ' + CONVERT(VARCHAR, spid) + '; '
FROM
    master..sysprocesses
WHERE
    dbid = DB_ID('Testes') -- Nome do database
    AND dbid > 4 -- Não eliminar sessões em databases de sistema
    AND spid <> @@SPID -- Não eliminar a sua própria sessão


IF (LEN(@query) > 0)
    EXEC(@query)

Deleting connections from a database via ALTER DATABASE

The above command is very practical and works without problems. However, between the time you delete the sessions and run your RESTORE DATABASE, for example, other connections may connect to the database. For this reason, the most recommended method for this operation is with ALTER DATABASE, placing the database in SINGLE_USER mode, where only a single user can connect at a time:

ALTER DATABASE Testes SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

The WITH ROLLBACK IMMEDIATE parameter causes all sessions to be closed without any warning and rolled back immediately. Now you can carry out your maintenance without the possibility of any other connection interfering with your commands. At the end of your maintenance, remember to return the bank to MULTI_USER mode, so that it can accept multiple connections again:

ALTER DATABASE Testes SET MULTI_USER
GO

Deleting all connections from all databases

As a plus, I will post here two solutions to eliminate all sessions from all databases (except the system ones).

Via KILL:

DECLARE @query VARCHAR(MAX) = ''

SELECT
    @query = COALESCE(@query, ',') + 'KILL ' + CONVERT(VARCHAR, spid) + '; '
FROM
    master..sysprocesses
WHERE
    dbid > 4 -- Não eliminar sessões em databases de sistema
    AND spid <> @@SPID -- Não eliminar a sua própria sessão


IF (LEN(@query) > 0)
    EXEC(@query)

Via ALTER DATABASE:
Placing all instance databases in SINGLE_USER mode:

EXEC sp_msforeachdb '
IF (DB_ID(''?'') > 4)
    EXEC (''ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'')'

Returning all instance databases to MULTI_USER mode:
In this case, I could not use the sp_msforeachdb stored procedure, since it does not list the databases in SINGLE_USER mode. In this case, I had to loop between the databases manually.

IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #Databases
SELECT IDENTITY(INT, 1,1) AS Id, name
INTO #Databases
FROM sys.sysdatabases
WHERE dbid > 4 -- Ignorar databases de sistema
    

DECLARE 
    @Contador INT = 1, 
    @Total_Databases INT = (SELECT COUNT(*) FROM #Databases),
    @Query VARCHAR(MAX)


WHILE(@Contador <= @Total_Databases)
BEGIN
    
    SELECT @Query = 'ALTER DATABASE [' + name + '] SET MULTI_USER;'
    FROM #Databases
    WHERE Id = @Contador
    
    EXEC(@Query)
    
    SET @Contador = @Contador + 1
    
END

That's it, dear readers.
Until next time!