Propriedade do banco de dados Redução automática do SQL Server

Propriedade do banco de dados Redução automática do SQL Server

SQL Server Auto Shrink option
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, discutiremos a propriedade de banco de dados Redução automática do SQL Server, que permite ao SQL Server reduzir automaticamente os arquivos do banco de dados quando o valor estiver configurado como True na opção de banco de dados. A operação automática de redução é feita pelo servidor banco de dados de redução automática recurso, que é o prato principal deste artigo.

Para evitar a atividade de encolher arquivos de dados / log sempre que o tamanho do arquivo exceder o espaço livre no arquivo de dados, o DBA (Administrador do Banco de Dados) deve fazer backup dos arquivos de log em intervalos regulares. Fazer backup de todo o banco de dados não é uma boa ideia; é preciso construir ou configurar também o log de transações. Os logs de transações aumentarão em números até ocuparem todo o espaço em disco disponível, se você não recuperá-los. Se você fizer backup do banco de dados, ele liberará o espaço livre para ser reutilizado novamente. O Administrador do banco de dados deve agendar um backup do log de transações para reduzir o tamanho dos arquivos de log em um tamanho justo.

O recurso de redução automática do SQL Server está desabilitado por padrão nos bancos de dados da instância do SQL Server. No cenário em que você tem um número de bancos de dados relativamente pequenos que aumentam para um tamanho maior por inserção de novas tuplas ou por um grande número de espaços vazios criados pela exclusão de várias tuplas, o SQL Server Auto Shrink se torna bastante útil nessa situação. Além disso, você não precisa se preocupar com a fragmentação do tamanho dos arquivos de banco de dados.

Ao reduzir o banco de dados, é necessário considerar operações de redução nos bancos de dados críticos e relativamente maiores como parte da manutenção. Além disso, este deve evitar executar o recurso de encolhimento manual; As conseqüências disso são que nunca se familiarizará com problemas relacionados à solicitação nova ou existente. No entanto, os arquivos de transação encolhendo são melhores que os arquivos de dados.

Maneira de ativar e desativar o encolhimento automático do SQL Server para o banco de dados?

Os usuários podem ativar e desativar a opção de redução automática do banco de dados usando o SSMS e o T-SQL nos dois sentidos.

Ativando o encolhimento automático do banco de dados usando o SSMS:

Os usuários podem ativar ou desativar essa opção nas propriedades do banco de dados, com uma tag Encolhimento automático. Aqui, True no menu suspenso ativará esta opção para o seu banco de dados.

Leia Também  Dicas adicionais para trabalhar em casa - SQLBlog

Opção de redução automática do SQL Server

Ative a redução automática do banco de dados usando o T-SQL:

Os usuários podem executar as instruções abaixo do T-SQL para ativar ou desativar o banco de dados de redução. Nós usamos o AdventureWorks banco de dados para a instrução T-SQL acima.

Efeitos do banco de dados diminuindo no desempenho da consulta

Efeitos negativos podem ser gerados no lado do desempenho da consulta se você ativar a opção de redução automática do banco de dados e as configurações de crescimento automático juntas para o banco de dados. Podemos manter um espaço para o crescimento dos arquivos de dados e também evitar alguns eventos frequentes de crescimento automático configurando o tamanho do banco de dados para um valor ideal ou, principalmente, todo banco de dados tem alguns parâmetros com os recursos de crescimento automático ativados.

Para esses bancos de dados, os recursos de redução automática devem ser ativados por nós quando o banco de dados é menor, uma e não mais operação CRUD, para possibilitar a redução de arquivos de dados e recuperar o espaço livre que provisionamos intencionalmente para manter nossos bancos de dados afastados do modo automático. eventos de crescimento. O espaço livre será liberado automaticamente nos arquivos de dados e arquivos de log periodicamente em prioridade seqüencial round-robin pelo recurso de redução automática, se vários bancos de dados tiverem ativado esse recurso.

Para bancos de dados de tamanho grande, crescimento automático e posteriormente, o encolhimento automático será executado, resultando em problemas de desempenho causados ​​pela fragmentação no nível do sistema. Para resumir tudo isso, em qualquer banco de dados, a redução automática não deve ser ativada com base nos seguintes motivos:

  • A execução dos algoritmos de redução automática do SQL Server sem nenhum objetivo definitivamente desperdiçará os recursos em grande quantidade

  • Se você executar o encolhimento automático ou manual do SQL Server, obviamente causará a fragmentação do índice e, eventualmente, os arquivos de dados também serão executados.

  • Se o servidor também estiver forçando os limites do subsistema de E / S, o encolhimento em execução poderá forçá-lo, resultando em longas filas de disco e possivelmente em tempos limite de E / S, isso consumirá os recursos de E / S do servidor e da CPU em grande quantidade

  • O desempenho do sistema será retardado pela fragmentação no nível do disco do sistema de arquivos, que é novamente o efeito direto de reduzir e aumentar arquivos de dados com frequência

Para entender mais sobre os eventos de crescimento e redução no SQL Server, leia este artigo, Obtenha detalhes dos eventos de crescimento e redução do banco de dados do SQL Server.

Para o banco de dados crítico, a operação de redução manual pode ser executada no nível do arquivo do banco de dados pelo especialista. A atividade manual do arquivo de redução pode ser realizada quando a operação de exclusão é executada e, posteriormente, o espaço foi recuperado. Devemos reconstruir os índices fragmentados na execução da operação de redução, pois a operação de redução 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 das DMVs. No entanto, a redução do arquivo de log deve ser feita manualmente, quando e quando necessário, e não deve fazer parte da atividade de manutenção regular.

Para monitorar regularmente a utilização do disco, o relatório de uso do disco pode ser analisado pelo usuário antes de executar a operação de redução manual usando o SSMS, que fornece uma visão geral dos dados e das informações de espaço de log para um banco de dados na tela. Se o usuário desejar obter um painel para o cálculo de Espaço Reservado e Espaço Livre para arquivos de banco de dados, o Relatório de Disco se tornará muito útil. No entanto, o relatório do disco contém as informações usando as DMVs do SQL Server. O relatório do disco está disponível no diretório abaixo usando o SSMS.

Banco de Dados >> Relatórios >> Relatórios Padrão >> Uso de Disco

Relatório de uso do disco

Aqui, temos um relatório de disco para o [AdventureWorks] base de dados. Estatísticas em tempo real dos arquivos de dados e de log podem ser vistas 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ção Reservado e Espaço OLTP em Memória Reservado.

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Leituras de uso do disco

Reduzir banco de dados usa automaticamente os trabalhos do SQL Server

Esses scripts podem ser executados agendando um trabalho pelo usuário para executar a operação de redução do banco de dados com a atividade do planejador. Como a primeira etapa do script, encontre o espaço livre no arquivo do banco de dados e reduza esse arquivo se os critérios definidos para o espaço livre forem correspondidos. O espaço livre deve ser calculado com a ajuda das DMVs do SQL Server.

Basicamente, o monitoramento do arquivo de banco de dados é realizado pelo administrador do banco de dados, que, por sua vez, executa etapas no banco de dados para monitorar o tamanho do arquivo do banco de dados. Se o limite predefinido for atravessado por qualquer arquivo, eles deverão executar essas etapas. Para que a atividade possa ser automatizada usando o trabalho do SQL Server para executar diariamente, semanalmente ou mensalmente em um horário predefinido.

A melhor prática é agendar esse trabalho apenas no arquivo de log do banco de dados e monitorar o espaço livre do arquivo de dados manualmente. Porque também pode afetar o desempenho da consulta. O DMV do SQL Server retornará o arquivo de dados e o arquivo de log no mesmo conjunto de resultados, portanto, o tipo de arquivo precisa ser bifurcado na instrução T-SQL do SQL Server DMV. Antes de reduzir o arquivo, o usuário pode aplicar a lógica de consulta no espaço total e no espaço livre do arquivo de log. Se os critérios de cálculo corresponderem à propriedade do arquivo, o arquivo será reduzido com o tamanho do arquivo de destino.

Verifique o espaço livre para os arquivos de banco de dados:

Se esses critérios corresponderem aos arquivos do banco de dados, por exemplo, se o Espaço livre for maior que (n) MB / GB, se n (%) o espaço livre for comparado ao 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 tamanho do arquivo, o administrador do banco de dados precisará explorar o log de transações.

Encolher arquivo de banco de dados:

Aqui, 5120 é um tamanho de arquivo de destino em MB. Portanto, o arquivo terá o tamanho de 5120 MB. Configure a consulta T-SQL com os critérios necessários na etapa da tarefa do SQL Server e agende-a para o horário de folga.

A opção de redução automática do SQL Server não pode ser habilitada para todos os bancos de dados. Especificamente, ajuda no banco de dados menor, que executa menos operações CRUD comparativamente.

Conclusão

Neste artigo, discutimos a propriedade do banco de dados Redução automática no SQL Server para reduzir dados e arquivos de log e remover o espaço não utilizado. Reduzir um banco de dados é uma operação cara e deve ser usada com cuidado.

Jignesh Raiyani
Últimas mensagens de Jignesh Raiyani (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br