Hey Guys!
Tudo beleza ?
Neste post eu gostaria de escrever sobre um recurso do SQL Server que ainda nos dias de hoje, vemos sendo implementados em instâncias de várias empresas e que faz com que todos os recursos (CPU, Disco, Memória..) sejam liberados após o último usuário se descontar do databases. Estou falando da propriedade Auto-Close, que é considerado por muitos, uma das PIORES práticas na administração de instâncias SQL Server.
Explicando o recurso Auto Close
De acordo com a documentação da Microsoft, quando o recurso Auto Close está ativado em um database, todos os recursos relacionados àquele database são liberados e o database é fechado após o último usuário ativo se desconectar desse database. Se um usuário tentar acessar novamente esse banco, os recursos do servidor são realocados novamente, o banco é reaberto e fica disponível.
Na teoria, esse recurso parece ser vantajoso, pois economiza recursos do servidor e só os utiliza quando for necessário. Entretanto, na prática o que ocorre é o inverso: Existe um custo para derrubar as conexões inativas, manipulação de arquivos e alocações de buffer para “desligar” o database quando ele é fechado automaticamente. Esse custo é ainda maior ao ter que “ligar novamente” o database quando um novo acesso é solicitado à esse database quando ele está “desligado”.
Como não é feito nenhum cache, se o banco for fechado pelo Auto-close e após 1 ms ele for requisitado novamente, o banco terá que desalocar todos os recursos e realocar tudo de novo em um intervalo de 1ms.. Imaginem uma instância em que isso ocorre com frequência. O overhead dessa operação, repetida várias e várias vezes, exige um esforço muito maior que deixar os recursos alocados indefinidamente, não justificando o uso dessa propriedade.
Além disso, esse recurso vai de encontro com os benefícios obtidos pelo Pool de conexão do SQL Server, muito utilizados por aplicações .NET, cuja proposta é reduzir o alto custo de ter que ficar abrindo e fechando conexões a cada comando enviado para o banco de dados.
Outro ponto negativo ao uso dessa propriedade é que você irá começar a ver vários e vários eventos no log do SQL Server com a mensagem “Starting up database seu_database”, conforme demonstrado abaixo:
Isso é muito ruim para administração da instância, pois acaba gerando um volume muito grande de informações no log da instância e desviando a atenção de alertas que realmente podem indicar um alerta ou problema na instância.
Para piorar ainda mais a vida do DBA que costuma ativar essa propriedade, algumas DMV’s do SQL Server, que você utiliza para performance tuning, validações e extração de informações, acabam tendo seus valores zerados cada vez que o database fica offline, fazendo com que você não tenha informações confiáveis para realizar esse tipo de análise.
Mais um ponto negativo do Auto Close, é que já vi alguns casos em que ele acaba gerando uma “confusão” na procedure não documentada master.dbo.sp_MSforeachdb, fazendo com que ela, no seu loop de iterações entre os databases, acabe não listando um database que está com Auto Close ativado, e com isso, não executando o comando que deveria ser executado. Isso ocorre por causa que essa procedure executa apenas os comandos em databases cuja situação seja “ONLINE”. Com o Auto Close ativado, é comum que ele fique no estado “In Recovery” ao sair do estado de inatividade quando um usuário fizer um acesso.
Se você ainda não está satisfeito com todos os argumentos apresentados, essa propriedade está marcada como deprecated (Link de referência), ou seja, será removida em alguma versão futura do SQL Server e não deve ser mais utilizada em novos ambientes e instâncias recém criadas.
Na verdade, você nem deveria estar utilizando o Auto Close.
Em que cenários o Auto Close deve ser utilizado?
Por ser considerado uma péssima prática entre os DBA’s SQL Server, é muito improvável que você encontre um cenário onde o Auto Close deve ser utilizado.
Um cenário em que penso que justificaria, é quando você tem um database em um servidor com poucos recursos de CPU e memória, e que é acessado uma vez por dia, por um job do SQL Agent ou uma query específica. Essa query executaria uma rotina de processamento lendo os dados desse database e após esse processamento, nenhum job, usuário ou aplicação faria leituras, escritas e nem nenhum tipo de acesso neste database.
Como você deve saber, no mundo real esse cenário é muito improvável de acontecer e difícil de ter essa garantia de que ninguém mais vai utilizar o database, fazendo com que mesmo que esse cenário exista, futuramente você pode correr o risco desse cenário mudar e sua instância sofrer esse overhead gerado pelo Auto Close.
Como identificar se um database está com o Auto Close ativado?
Para identificar as instâncias que estão com a propriedade Auto Close ativada, basta clicar com o botão direito em um database, selecionar a opção “Properties”
Nesta tela, selecione a categoria “Options” e procure pela propriedade “Auto Close” no Grid.
Como identificar os databases com o Auto Close ativado na instância utilizando T-SQL?
Para identificar os databases com o Auto Close ativado na instância utilizando T-SQL, você pode executar a query abaixo:
|
1 2 3 |
SELECT database_id, [name], user_access_desc, state_desc FROM sys.databases WHERE is_auto_close_on = 1 |
Result:
Como desativar o Auto Close utilizando T-SQL?
Para desativar a propriedade Auto Close de um database na sua instância, utilize o comando abaixo:
|
1 2 3 4 5 |
USE [master] GO ALTER DATABASE [dirceuresende] SET AUTO_CLOSE OFF GO |
Caso você queira aplicar esse comando em todos os databases da sua instância, você pode utilizar esse comando:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO DECLARE @Comando VARCHAR(MAX) = '' SELECT @Comando += 'ALTER DATABASE [' + [name] + '] SET AUTO_CLOSE OFF; ' FROM sys.databases WHERE is_auto_close_on = 1 EXEC(@Comando) |
É isso aí!
Espero que tenham gostado dessa dica.
Um abraço e até o próximo post.
sql server banco de dados how to identify disable como identificar desativar o que é auto close property propriedade option opção todos os databases
sql server banco de dados how to identify disable como identificar desativar o que é auto close property propriedade option opção todos os databases
Portuguese 
English
Vou usar perfeita muito clara as explicações Parabéns.
Obrigado, Jorge.
Fico feliz em saber que você gostou do artigo.
Abraço!
Muito interessante o post Dirceu! Gostei dos pontos observados e na simplicidade da explicação. É uma opção simples mas que gera uma série de impactos no ambiente e muito gente desconhece a sua utilidade.
Abraço.
Luiz Vitor
Opa! Excelente post Dirceu! Tive um problema essa semana num ambiente de produção, após migrar um conjunto de databases de um cliente para dentro do datacenter onde trabalho; eu não tinha checado essa propriedade e percebi o problema ao executar a procedure master.dbo.sp_MSforeachdb. Ela não retornou 1 dos mais de 20 databases que foram migrados no relatório, e ao abrir o log do SQL haviam vários registros dos databases reiniciando… a maioria estava com auto close ativado, foi então quando fiz a análise e alterei todos os auto close para off. Incluí então essa checagem para o checklist de migração 🙂
Dica preciosa essa e totalmente prática… Valeu por compartilhar!
Boa dica Dirceu, muita gente tem esse parâmetro habilitado e nem sabe o porque. Na verdade, quando utiliza-se SQL Server Express é muito comum habilitar esse parâmetro para economizar recursos, pode então ocorrer, do desenvolvedor ou algo assim, enviar um backup para o cliente e o cliente que não usa Express, ficar lá com esse parâmetro desnecessário ativado.