Neste artigo, discutiremos a propriedade do SQL Server Auto Shrink database, que permite que o SQL Server encolha automaticamente os arquivos do banco de dados quando o valor é configurado como Verdadeiro na opção banco de dados. A operação de encolhimento automático é feita pelo recurso de banco de dados auto shrink do servidor, que é o principal curso deste artigo.
Para evitar a atividade de encolhimento de arquivos de dados/log sempre que o tamanho do arquivo ultrapassa o espaço livre no arquivo de dados, o DBA (Database Administrator) deve fazer backup dos arquivos de log em um intervalo regular. Fazer o backup de toda a base de dados não é uma boa ideia; é preciso construir ou configurar o log de transações também. Os logs de transações irão aumentar em números até ocupar todo o espaço disponível em disco se você não fizer backup deles. Se você fizer backup do banco de dados, ele libera o espaço livre para ser reutilizado novamente. O Administrador do banco de dados deve agendar um backup do log de transação para reduzir o tamanho dos arquivos de log em um tamanho justo.
A função Auto Shrink do SQL Server está desativada por padrão nos bancos de dados de instância do SQL Server. No cenário onde você tem um número relativamente pequeno de bancos de dados que crescem para um tamanho maior, seja pela inserção de novos tuples ou por um grande número de espaços vazios criados pela exclusão de vários tuples, o Auto Shrink do SQL Server se torna bastante útil nesta situação. Além disso, você não precisa se preocupar com a fragmentação do tamanho dos arquivos do banco de dados.
Enquanto encolhe o banco de dados, você deve considerar operações de encolhimento tanto em bancos de dados críticos quanto em bancos relativamente maiores como parte da manutenção. Além disso, este deve evitar correr o recurso de encolhimento manual; as consequências de fazer isso é que nunca se estará familiarizado com questões relativas ao pedido novo ou existente. No entanto, o encolhimento dos ficheiros de transacção é melhor do que o encolhimento dos ficheiros de dados.
Como ativar e desativar o encolhimento automático do SQL Server para o banco de dados?
Usuários podem ativar e desativar a opção de encolhimento automático do banco de dados usando SSMS e T-SQL de ambas as maneiras.
Ativar o encolhimento automático do banco de dados usando SSMS:
Os utilizadores podem activar ou desactivar esta opção a partir das propriedades do banco de dados, com uma etiqueta de Encolhimento Automático. Aqui, True no drop-down irá habilitar esta opção para o seu banco de dados.
Ativar encolhimento automático do banco de dados usando T-SQL:
Os usuários podem executar as instruções abaixo do T-SQL para ativar ou desativar o banco de dados de encolhimento. Nós usamos a base de dados AdventureWorks para a declaração T-SQL acima.
|
1
2
3
4
5
6
|
–Enable Auto Shrink for the database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
>–Desactivar Auto Shrink para a base de dados AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Efeitos da contracção da base de dados na performance da consulta
Bad effects can be raised on the query performance side if you turn on the database auto shrink option and auto-growth settings together for the database. Podemos manter algum espaço para que os arquivos de dados cresçam e também evitar alguns eventos frequentes de autocrescimento ao configurar o tamanho do banco de dados para um valor ótimo ou a maioria dos bancos de dados tem alguns parâmetros com funcionalidades de autocrescimento ativadas.
Para tais bancos de dados, funcionalidades de auto-encolhimento devem ser ativadas por nós quando o banco de dados é menor, uma e nenhuma operação CRUD mais, de modo que possa tornar possível o encolhimento dos arquivos de dados e recuperar o espaço livre que provisionamos propositadamente para manter nossos bancos de dados longe de eventos de autocrescimento. O espaço livre será liberado automaticamente nos arquivos de dados e arquivos de log periodicamente na prioridade sequencial round-robin pelo recurso Auto shrink se vários bancos de dados tiverem ativado esse recurso.
Para bancos de dados de grande porte, auto-growth, e depois disso, o auto shrink será executado que resultará em problemas de performance conduzidos pela fragmentação a nível do sistema. Para resumir tudo isso, para qualquer banco de dados, o auto shrink não deve ser ativado com base nas seguintes razões:
- Executar os algoritmos de auto shrink do SQL Server sem nenhum propósito irá definitivamente desperdiçar os recursos em grande quantidade
- Se você executar o auto shrink do SQL Server ou o shrink manual, obviamente irá causar a fragmentação do índice e isto eventualmente executará os arquivos de dados também
- Se o servidor também estiver forçando os limites do subsistema IO, executar o shrink pode empurrá-lo para cima, resultando em longas filas de disco e, possivelmente, tempos de espera de IO, isso irá consumir os recursos de IO e CPU do servidor em uma grande quantidade
- O desempenho do sistema será retardado pela fragmentação do nível do disco do sistema de arquivos, que é novamente o efeito direto de fazer encolhimento e crescimento dos arquivos de dados frequentemente
Para entender mais sobre Crescimento e Eventos de Encolhimento no SQL Server, leia este artigo, Obtenha detalhes sobre Crescimento e Eventos de Encolhimento do Banco de Dados do SQL Server.
Para o banco de dados crítico, a operação de encolhimento manual poderia ser executada no nível do arquivo do banco de dados pelo especialista. A atividade do arquivo shrink manual pode ser feita quando a operação de exclusão é executada, e depois disso, o espaço foi recuperado. Nós devemos reconstruir os índices fragmentados após a execução da operação de encolhimento, pois a operação de encolhimento pode levar à fragmentação do índice. A porcentagem de fragmentação do índice pode ser verificada pelo usuário com o uso de instruções T-SQL de DMV’s. Entretanto, o encolhimento do arquivo de log deve ser feito manualmente como e quando necessário e não deve fazer parte da atividade de manutenção regular.
Para monitorar o uso do disco regularmente, o relatório de uso do disco poderia ser analisado pelo usuário antes de executar a operação manual de encolhimento usando SSMS, o que lhe dá uma visão dos dados e informações de espaço de log para um banco de dados no display. Se o usuário gostaria de obter um painel para um cálculo de Espaço Reservado e Espaço Livre para arquivos de banco de dados, então o Relatório de Disco se torna muito útil. No entanto, o relatório em disco habita a informação utilizando DMV’s do SQL Server. O relatório de disco está disponível no diretório abaixo usando SSMS.
Base de Dados >> Relatórios >1895> Relatórios Padrão >> Uso do Disco
Aqui, temos um Relatório de Disco para o banco de dados. Estatísticas em tempo real para os arquivos de dados e arquivos de log podem ser vistos pelo usuário. Este relatório incorpora informações primárias de Espaço Total Reservado, Espaço de Arquivos de Dados Reservado, Espaço de Log de Transações Reservado, e Espaço OLTP Reservado em Memória.
Banco de Dados Encolhido utiliza automaticamente os Jobs do SQL Server
Tais scripts poderiam ser executados agendando um job pelo usuário para executar a operação de encolhimento do banco de dados com a atividade do agendador. Como primeiro passo no script, encontre o espaço livre no arquivo de banco de dados e depois encolha esse arquivo se os critérios definidos para o espaço livre forem correspondidos. O espaço livre deve ser calculado com a ajuda do SQL Server DMV’s.
Basicamente, o monitoramento do arquivo de banco de dados é realizado pelo administrador do banco de dados, que por sua vez passos no banco de dados para monitorar o tamanho do arquivo de banco de dados. Se o limite pré-definido for ultrapassado por qualquer ficheiro, então eles precisam de executar tais passos. Para que a atividade possa ser automatizada usando o trabalho do SQL Server para executar em uma base diária, semanal ou mensal em um horário pré-definido.
A melhor prática é agendar este trabalho no arquivo de log de banco de dados apenas e monitorar o espaço livre do arquivo de dados manualmente. Porque isso pode afetar o desempenho da consulta também. O SQL Server DMV irá retornar o arquivo de dados e o arquivo de log no mesmo conjunto de resultados, então o tipo de arquivo precisa bifurcar na instrução T-SQL do SQL Server DMV. Antes de encolher o arquivo, o usuário pode aplicar lógica de consulta no espaço total e espaço livre do arquivo de log. Se o critério de cálculo for compatível com a propriedade do ficheiro, então o ficheiro irá encolher com o tamanho do ficheiro alvo.
Verifica o espaço livre para os ficheiros da base de dados:
Se esse critério for compatível com os ficheiros da base de dados, por exemplo, se o espaço livre for maior que (n) MB/GB, se n(%) espaço livre for comparado com o espaço total, e muito mais. Quando o espaço livre não estiver disponível para o arquivo de log e o tamanho do arquivo estiver próximo do valor máximo do parâmetro de tamanho do arquivo, então o administrador do banco de dados precisa procurar no log de transações.
Tempo de base de dados encolhido:
|
1
|
DBCC SHRINKFILE(file_name, 5120);
|
>
Aqui, 5120 é um tamanho de ficheiro alvo em MB. Assim, o arquivo se tornará um tamanho de 5120 MB. Configure a Consulta T-SQL com os critérios necessários no passo de trabalho do SQL Server e programe-a para as horas de funcionamento.
A opção Auto shrink do SQL Server não pode ser ativada para todas as bases de dados. Especificamente, ela ajuda para a base de dados menor, que realiza comparativamente menos operações CRUD.
Conclusão
Neste artigo, discutimos a propriedade Auto Shrink database no SQL Server para encolher dados e arquivos de log e remover espaço não utilizado. Encolher uma base de dados é uma operação cara e deve ser usada com cuidado.
- Autor
- Recent Posts
Ver todos os posts de Jignesh Raiyani
- Página Life Expectancy (PLE) no SQL Server – 17 de Julho, 2020
- Como automatizar o particionamento de tabelas no SQL Server – 7 de julho de 2020
- Configurar o SQL Server Always On Availability Groups no AWS EC2 – 6 de julho de 2020
>
>