Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Como eliminar todas as conexões de um database no SQL Server

Post Views 21,005 views
Reading time 3 minutes

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:

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:

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:

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:

Via ALTER DATABASE:
Colocando todos os databases da instância no modo SINGLE_USER:

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.

É isso aí, caros leitores.
Até a próxima!