SQL Server – Alterei o Max Server Memory para 0 e agora não consigo conectar na instância

Hey guys!
Nesse post, eu gostaria de compartilhar com vocês a solução de um problema que é até comum de acontecer e de vez em quando, alguém surge nos grupos do Whatsapp e Telegram com esse problema, que é configuração incorreta do parâmetro Max Server Memory, definindo um valor baixo demais e como consequência disso, você não consegue mais se conectar na instância:

Esse tipo de problema ocorre muito por conta de confusão na hora de especificar o tamanho e coloca 128 MB achando que é 128 GB, por exemplo, ou especialmente devido o valor 0 na configuração do max server memory:

• Se você alterou a configuração para 0 utilizando a interface do SSMS, ele irá ajustar automaticamente o tamanho máximo para 16 MB (SQL 2005-2008 R2), 64 MB (SQL 2012 32 bits) ou 128 MB (SQL 2012+ 64 bits)
• Se você alterou a configuração para 0 utilizando T-SQL (sp_configure), ele ira ajustar o tamanho máximo para o padrão (2.147.483.647 MB = 2 Petabytes)

Solução 1 – Iniciando o SQL em modo mínimo

Existem várias formas de se resolver esse problema, como iniciando o binário do SQL Server via linha de comando, mas eu acho mais fácil alterar o parâmetro de inicialização do serviço no SQL Server Configuration Manager:

Caso você não localize o SQL Configuration Manager no seu servidor, basta abrir o Menu Iniciar > Executar (WinKey + R) e digitar:

• SQLServerManager10.msc (SQL Server 2008)
• SQLServerManager11.msc (SQL Server 2012)
• SQLServerManager12.msc (SQL Server 2014)
• SQLServerManager13.msc (SQL Server 2016)
• SQLServerManager14.msc (SQL Server 2017)
• SQLServerManager15.msc (SQL Server 2019)

Agora que o SQL Configuration Manager foi aberto, localize o serviço do SQL Server que está com problemas e clique com o botão direito e Propriedades:

Na tela que foi aberta, selecione a aba “Startup parameters” e adicione o parâmetro -f, que serve para iniciar o SQL Server com as configurações mínimas, o qual ignora algumas configurações do SQL Server e parâmetros de memória, tempdb é configurado no menor tamanho possível, somente um usuário pode se conectar e o CHECKPOINT não é executado.

Após adicionar esse parâmetro, reinicie o serviço do SQL Server:

Agora abra o Prompt de comando como Administrador:

Utilize o SQLCMD para se conectar à sua instância através do comando “sqlcmd -S servidor\instancia” ou “sqlcmd -S servidor,porta” (Quero aprender mais sobre o SQLCMD):

Caso você tente se conectar pelo SSMS, você irá se deparar com a seguinte mensagem de erro:

Login failed for user ‘dirceu.resende’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Uma vez que você conseguiu se conectar na instância, agora é possível alterar os parâmetros de memória:

Result:

Após conseguir realizar a alteração, remova o parâmetro -f que adicionamos e reinicie o serviço:

Após reiniciar o serviço sem o parâmetro -f, a instância voltou a funcionar normalmente:

Solução 2 – Dedicated Admin Connection (DAC)

Uma outra forma (e até mais prática) é utilizando a Dedicated Admin Connection, também conhecida como DAC, que é uma conexão especial para resolver problemas onde o login não está sendo mais possível de ser realizado, seja uma configuração de memória muito baixa, uma trigger de logon impedindo os logins, etc.. Por padrão, a conexão DAC remota é desativada, então você deve habilitar a conexão remota DAC ou precisará estar logado no servidor para conseguir utilizá-la (DAC local).

Para saber mais sobre a conexão DAC e como habilitar o uso dessa conexão remotamente, acesse o artigo Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server e também o artigo SQL Server – Como conectar utilizando a conexão DAC (Dedicated Admin Connection) sem o SQL Browser.

Para se conectar na instância travada utilizando o DAC pelo SQLCMD, basta adicionar o parâmetro -A:

Nos testes que realizei com pouca memória (128 MB), o serviço do SQL não conseguia ficar nem 10s online e já parava.. Então eu subia o serviço e logo em seguida, já utilizava a conexão DAC para alterar a configuração de memória:

Espero que vocês tenham gostado dessa dica rápida e que ela possa ser útil no dia a dia de vocês.
Um abraço e até mais!