SQLShack

este artigo explora o uso de vários arquivos de Log de transação do SQL Server e o processo de remoção do arquivo de log de transação secundário.

introdução

por padrão, um banco de dados do SQL Server contém um arquivo de dados primário e um arquivo de log de transações. É uma boa prática configurar vários arquivos de dados e dividir dados em vários arquivos de dados. Podemos ter esses arquivos de dados em unidades de armazenamento separadas para ter vários IO. Ele ajuda o gerenciamento de dados, melhora o desempenho, planeja abordagens de backup de acordo com grupos de arquivos.

podemos ter vários arquivos de log de transações para um banco de dados.

o SQL Server usa os vários arquivos de log de transações em ordem sequencial. Suponha que tenhamos dois arquivos de log de transações. O SQL Server usa um arquivo de log por vez e, uma vez cheio, o SQL Server usa outro arquivo de log. Como o SQL Server não o usa em paralelo, não obtemos nenhum benefício de desempenho dele. Idealmente, devemos ter apenas um arquivo de log de transações por banco de dados.Vamos supor que temos um banco de dados de produção e recebemos um alerta crítico de que o disco está ficando sem espaço. Após a investigação, descobrimos que esta unidade contém um arquivo de log de transações. Devido a transações ativas, este arquivo de log de transações do SQL Server está cheio. Tentamos reduzir o arquivo de log, mas não funcionou. Fazemos backup do log de transações também, mas devido a transações ativas, ele não pôde liberar o espaço necessário. Adicionar outro arquivo de log de transações em um disco separado com espaço livre resolverá esse problema.

como o SQL Server usa o modo serial para gravar dados em um arquivo de log de transação, devemos remover o arquivo de log adicional mais tarde. Vamos explorar o processo de remoção de um arquivo de log adicional.

crie um banco de dados com vários arquivos de Log de Transações do SQL Server

conecte-se a uma instância SQL no SQL Server Management Studio. Clique com o botão direito do mouse no nó bancos de dados no painel Object Explorer e clique no novo comando Database:

Ele abre a Nova janela de base de dados. Especifique um nome de banco de dados e adicione outro arquivo de log de transações. Para fins de teste, desativamos o crescimento automático de arquivos de log de transações. O tamanho inicial do arquivo de log de transação é de 8MB:

Depois que tudo estiver definido, clique no botão OK para criar um banco de dados em um padrão de dados/diretório do arquivo de log. Crie uma tabela e insira dados em:

Ler o arquivo de log virtual de status no SQL Server arquivos de log de transação

No artigo, o SQL Server Log de Transações Arquitetura, exploramos interna do arquivo de log de transação. Cada arquivo de log de transação consiste em vários arquivos de log virtuais. Um arquivo de log de transação é uma combinação de vários arquivos de log virtual (VLF). A captura de tela a seguir mostra a arquitetura física e lógica do arquivo de log:

SQL Server estatísticas de um banco de dados com o mínimo de VLF com base no tamanho do log inicial e auto crescer arquivo (com base no auto-configuração de crescimento). Na imagem a seguir, temos um vislumbre do arquivo de log de transações do SQL Server:

No caso de um único arquivo de log de transações, o SQL Server usa uma circular virtual caminho do arquivo de log. Podemos verificar o número de arquivos de log virtuais e o seu estado com o seguinte:

1. DBCC LOGINFO(‘Banco de dados’) – é uma declaração antiga e funciona com todas as versões do SQL Server
2. exibição de gerenciamento Dinâmico sys.dm_db_log_info (DBID). Ele está disponível no SQL Server 2016 SP2 ou posterior

qualquer um dos comandos pode ser usado para a verificação VLF. Para este artigo, vamos usar a exibição de gerenciamento dinâmico (DMV):

Na imagem acima, temos verificado que o nosso banco de dados de exemplo contém dois arquivos de log de transações (file_id 2 e file_id 3).

• File_id 2 tem o active VLF (vlf_active=1 e vlf_status=2)
• File_id 3 não tem qualquer ativo VLF (vlf_active=0 e vlf_status=2)

Vamos inserir mais alguns registros na tabela para que o active VLF vai mudar:

O espaço de log de transação de monitoramento do uso e VLF estado será feito com DMV (sys.dm_db_log_space_usage):

• VLF estado

  1 2	SELECIONE DB_name() como DatabaseName,File_ID como transaction_log_file_ID, vlf_active , vlf_status FROM sys.dm_db_log_info(DB_ID(‘MultileLogFiles’));

• uso do log de Transações (utilizados e espaço livre)

  1 2 3 4 5

  SELECIONE total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes – used_log_space_in_bytes)*1.0/1024/1024 COMO free_log_space_in_MB FROM sys.dm_db_log_space_usage;

Temos o seguinte resultado das consultas acima:

• No lado esquerdo, vemos que, uma vez que o principal arquivo de log (file_id 2) se torna completo, ele se move para o próximo arquivo de log (file_id 3). Neste ponto, temos VLF ativo em arquivos de log primários e secundários. Em um banco de dados com um modelo de recuperação completa, precisamos de um backup do log de transações para que ele marque VLF como inativo
• a saída do lado direito (usando DMV) mostra o arquivo de log de transações usado espaço 8,51 MB. Conforme você se lembra, definimos o tamanho de 8 MB para cada arquivo de log de transações e desativamos o crescimento automático do log. Quando o arquivo de log principal estiver cheio (a 8 MB), o SQL Server mudará para o arquivo de log de transação secundária

remova o arquivo de log de transação secundário do SQL Server

para remover o arquivo de log de transação secundária (ID do Arquivo 3), usaremos a instrução ALTER database editada.

adicionaremos a cláusula remover arquivo e especificaremos o arquivo que queremos remover:

a Execução desta instrução resultará na seguinte mensagem de erro:

Nota: O active arquivo de log de transação não pode ser removido.

Anteriormente, vimos que, uma vez que o arquivo de log principal fica cheio, o SQL Server usa o arquivo de log secundário. Precisamos fazer um log de transação secundário vazio, para que possamos removê-lo.

no banco de dados SQL com um modelo de recuperação completa, usamos backups de log de transações para que o SQL Server possa truncar os logs. Podemos precisar de vários backups de log para isso, dependendo do tamanho do log de transações, VLF ativo e transação ativa.

Vamos realizar um completo banco de dados e backup de log de transação:

• backup Completo do banco

  1	Banco de dados de backup MultipleLogFiles to disk=’C:\Temp\MultipleLogFiles.bak’

• backup de log de Transação

  1	registo de cópia de segurança MultipleLogFiles to disk=’C:\Temp\MultipleLogFiles_log.trn’

uma Vez que o backup for concluído, verifique o VLF estado. O VLF ativo deve estar no primeiro arquivo de log (primário) para que possamos remover o arquivo de log secundário. Verificamos que apenas o arquivo de log primário (file_id 2) está ativo (status VLF 2):

agora, o arquivo de log de transação secundária deve ser removido sem problemas. Vamos executar a instrução ALTER database editada novamente. O secundário de log de transação ficheiro será removido:

uma Vez que o secundário do arquivo de log de transação é removido, verifique-o usando a GUI, bem como T-SQL com vista do sistema sys.dado:

O secundário removido arquivo de log de transação ainda está presente, conforme a captura de tela a seguir:

Agora clique com o botão direito do mouse no banco de dados e exibir os arquivos existentes. Vemos o arquivo de log de transações removido na GUI também. Mas porquê?

Vamos executar editada instrução Alter database novamente e veja se o SQL Server novamente, remove o arquivo de log de transação.

Nós recebemos a mensagem de que o SQL Server não pôde localizar o arquivo de log especificado:

SQL Server remove o arquivo de log de transações após um backup de log subseqüentes. Vamos ter um outro backup de log e verifique se que o arquivo de log de transação ainda existe:

Verifique o arquivo de log de transação em ambos GUI e métodos T-SQL. Vemos que o arquivo de log de transações removido não aparece agora.

conclusão

neste artigo, exploramos o uso do log de transações secundário do SQL Server e o processo de removê-lo. Você deve evitar o uso de vários arquivos de log de transações, especialmente no banco de dados de produção. Você deve fazer um backup de banco de dados antes de planejar qualquer atividade e fazê-lo em horas sem produtividade.

• Autor
• Posts Recentes

Como uma certificação MCSA e Microsoft Certified Trainer em Gurgaon, na Índia, com 13 anos de experiência, Rajendra trabalha para uma variedade de empresas de grande porte com foco em otimização de desempenho, monitoramento, alta disponibilidade e recuperação de desastres estratégias e implementação. Ele é autor de centenas de artigos autorizados sobre SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git e tecnologias relacionadas que foram visualizadas por mais de 10 milhões de leitores até o momento. Ele é o criador de uma das maiores coleções online gratuitas de artigos sobre um único tópico, com sua série de 50 partes no SQL Server sempre em grupos de disponibilidade. Com base em sua contribuição para a comunidade SQL Server, ele foi reconhecido com vários prêmios, incluindo o prestigioso “melhor autor do ano” continuamente em 2020 e 2021 na SQLShack.Raj está sempre interessado em novos desafios por isso, se você precisar de ajuda de consultoria sobre qualquer assunto coberto em seus escritos, ele pode ser alcançado em rajendra.gupta16 @ gmail.com
Ver todos os posts por Rajendra Gupta

postagens mais Recentes por Rajendra Gupta (ver todas)

• Use o BRAÇO de modelos para implantar o Azure recipiente de instâncias do SQL Server Linux imagens – 21 de dezembro de 2021
• área de trabalho Remota de acesso da AWS RDS para SQL Server com o Amazon RDS Personalizada – dezembro 14, 2021
• Armazenamento de arquivos do SQL Server no Armazenamento Persistente para Azure Recipiente Instâncias de dezembro de 10, 2021