Olá pessoal,
Boa noite.
Hoje venho trazer uma dica rápida para quem já queria fazer um restore rápido, um alter database ou qualquer comando que necessite de um lock exclusivo em um database, mas haviam usuários executando queries no banco, e aí você recebe uma mensagem de erro como essa 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.
Neste caso, devemos garantir que todas as conexões existentes que utilizem esse database sejam desconectadas e depois tentar executar nosso comando novamente. A melhor forma de fazer isso, é entrando em contato com os usuários e alinhando a sua necessidade para que eles mesmos fechem suas conexões e terminem de executar suas queries.
Nunca é recomendável realizar o KILL das conexões sem o consentimento dos usuários, a menos que seja algo crítico ou uma manutenção programada que já havia sido alinhada anteriormente.
Eliminando as conexões de um database via KILL
Caso seja realmente necessário eliminar manualmente as conexões que estão utilizando um database, você pode utilizar esse pequeno trecho de código T-SQL para realizar essa tarefa:
|
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) |
Eliminando as conexões de um database via ALTER DATABASE
O comando acima é bem prático e funciona sem problemas. Entretanto, pode ocorrer de entre o tempo de você eliminar as sessões e executar o seu RESTORE DATABASE, por exemplo, outras conexões se conectem no banco. Por este motivo, o método mais recomendável para essa operação é com ALTER DATABASE, colocando o banco de dados no modo SINGLE_USER, onde somente um único usuário pode se conectar por vez:
|
1 2 |
ALTER DATABASE Testes SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO |
O parâmetro WITH ROLLBACK IMMEDIATE faz com que todas as sessões sejam fechadas sem qualquer aviso e feito o rollback imediatamente. Agora você pode realizar suas manutenções sem possibilidade de qualquer outra conexão interferindo nos seus comandos. Ao final da sua manutenção, lembre-se de voltar o banco para o modo MULTI_USER, para que ele volte a aceitar múltiplas conexões novamente:
|
1 2 |
ALTER DATABASE Testes SET MULTI_USER GO |
Eliminando todas as conexões de todos os databases
Como um plus, vou postar aqui duas soluções para eliminar todas as sessões de todos os databases (menos os de sistema).
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:
Colocando todos os databases da instância no modo SINGLE_USER:
|
1 2 3 |
EXEC sp_msforeachdb ' IF (DB_ID(''?'') > 4) EXEC (''ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'')' |
Voltando todos os databases da instância para o modo MULTI_USER:
Neste caso, não pude utilizar a stored procedure sp_msforeachdb, uma vez que ela não lista os databases no modo SINGLE_USER. Neste caso, tive que fazer o looping entre os databases manualmente.
|
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 |
É isso aí, caros leitores.
Até a próxima!
Portuguese 
English