Hello people,
Good evening.
Today I bring a quick tip for those who already wanted to do a quick restore, an alter database or any command that requires an exclusive lock on a database, but there were users running queries in the database, and then you get 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 using 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 need for them to close their connections themselves and finish executing their queries.
It is never recommended to KILL connections without users' consent unless it is critical or a scheduled maintenance that was previously aligned.
Deleting database connections via KILL
If you really need to manually drop connections using a database, you can use this short T-SQL snippet to accomplish this task:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 database connections via ALTER DATABASE
The above command is very practical and works without problems. However, it may occur between the time you drop sessions and run your RESTORE DATABASE, for example, other connections connect to the database. For this reason, the most recommended method for this operation is with ALTER DATABASE, putting the database in SINGLE_USER mode, where only a single user can connect at a time:
|
1 2 |
ALTER DATABASE Testes SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO |
The WITH ROLLBACK IMMEDIATE parameter causes all sessions to close without warning and roll back immediately. Now you can perform your maintenance without 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:
|
1 2 |
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 system ones).
Via KILL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
Putting all instance databases in SINGLE_USER mode:
|
1 2 3 |
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 as it does not list the databases in SINGLE_USER mode. In this case, I had to loop between databases manually.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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.
To the next!
