Fala pessoal!
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:
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 4096; -- Altere para a quantidade desejada GO RECONFIGURE; GO |
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:
1 2 |
net start "SQL Server (SQL2017)" sqlcmd -S localhost\sql2017 -A -Q "EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sys.sp_configure 'max server memory', 4096; RECONFIGURE;" |
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!