Simulando – Minimal Logging – Microsoft SQL Server 2008 R2 e 2012.

Galera, bom dia.

Tudo bem?

Gostaria de compartilhar com vocês neste artigo um conceito que coloquei em prática há alguns dias. Estou me referindo ao chamado Minimal Logging, fiquei muito surpreso ao usar esta prática, algo que me ajudou muito a entender um pouco mais sobre o processo de geração de Logs de Transações em um banco de dados.

Vou então tentar apresentar de uma forma simples esta prática, como também, os possíveis cenários de uso, cuidados que devemos ter, quais operações e operadores podem ser utilizadas.

 

Introdução

O Minimal Logging(Log Mínimo), consiste basicamente em orientar o Microsoft SQL Server, a trabalhar com um uso restrito na geração de informações específicas que envolvam e recuperação de dados sem qualquer tipo de suporte a pontos específicos de restauração.

O uso do Minimal Logging só pode ser realizado com bancos de dados que apresentem os Modelos de Recuperação Simple ou Bulk – Logged. Podemos considerar o Minimal Logging com um contra ponto ao uso de outras práticas para armazenamento do Log de Transações, sua forma de trabalho simples permite a recuperação de informações de uma maneira mais rápida.

Considerado mais eficiente que o Full Logging, reduzindo a possibilidade que operações que envolvam um volume considerável de dados, venham a encher o log de transações.

Para aqueles que não conhecem os Modelos de Recuperação, abaixo apresento um breve resumo sobre os Modelos de Recuperação existentes no Microsoft SQL Sever.

Modelos de Recuperação de Banco de Dados

Os modelos de recuperação são projetados para controlar a manutenção de log de transações, possuem relação direta com as estruturas de armazenamento dos dados que formam um banco de dados.

Com base no modelo de recuperação definido para um banco, podemos sofrer alguns impactos em nosso ambiente, logicamente este impacto não tem a finalidade de prejudicar o funcionamento deste ambiente, ao contrário, eles existem para possibilitar outras formas de se trabalhar de acordo com o volume de informações existentes, sobre um determinado banco de dados.

Existem três modelos de recuperação: Simple, Completo e Bulk-logged, geralmente um banco de dados usa o modelo de recuperação completa ou o modelo de recuperação simples.

A Tabela 1 resume os Modelos de Recuperação:

Modelo de recuperação Descrição Exposição à perda de trabalho Recuperação pontual? 
Simples Sem backups de log.

Reclama espaço de log automaticamente para manter requisitos de espaços pequenos, eliminando essencialmente a necessidade de gerenciar o espaço de log de transações.

As alterações desde o backup mais recente estão desprotegidas. No caso de um desastre, essas alterações devem ser refeitas. Só pode recuperar até o fim de um backup.
Completo Requer backups de log.

Nenhum trabalho é perdido devido a um arquivo de dados perdido ou danificado.

Pode executar uma recuperação pontual (por exemplo, antes de um erro de aplicativo ou usuário).

Geralmente nenhum.

Se a parte final do log estiver danificada, as alterações desde o backup de log mais recente deverão ser refeitas.

Pode executar uma recuperação pontual, supondo que seus backups estejam concluídos até aquele ponto.
Bulk-logged Requer backups de log.

Um suplemento do modelo de recuperação completa que permite operações de cópia em massa de alto desempenho.

Reduz o uso de espaços de log usando o mínimo de registro em log para a maioria das operações em massa.

Se o log estiver danificado ou se ocorreu registro de operações em massa desde o backup de log mais recente, as alterações desde o último backup deverão ser refeitas.

Caso contrário, nenhum trabalho será perdido.

Pode recuperar até o final de qualquer backup. Não há suporte para recuperação pontual.

Tabela 1 – Modelos de Recuperação de Banco de Dados.

 

Operadores e operações que podem ser utilizadas com o Minimally Logged

Quando pensamos em fazer uso do Minimal Logging, temos que ter em mente que somente algumas operações ou operadores podem ser utilizados para forçar a geração mínima de logs.

A seguir destaco a lista de operações que podem ser registradas de forma mínima no log quando estamos utilizando os Modelos de Recuperação Simple ou Bulk-Logged:

 

  • Bulk import operations (BCP, BULK INSERT e INSERT..SELECT)
  • SELECT INTO;
  • TRUNCATE;
  • INSERT SELECT a partir do Microsoft SQL Server 2008, pode ser manipulada e registrada;
  • Atualizações parciais que apresentem tipos de dados considerados de grande tamanho, o que façam uso da claúsula Write;
  • CREATE INDEX;
  • ALTER INDEX REBUILD;
  • DROP TABLE;
  • Partition Switch; e
  • Merge, caso a Trace Flag 610 esteja habilitada.

 

 

Pré-requisitos para uso do Minimal Logging (Log Mínimo):

Existem alguns pré-requisitos para prover e permitir o uso do Log Mínimo:

 

  • As tabelas não podem estar envolvidas com Replicação;
  • A claúsula TABLOCK deve ser declarada, especificando que um bloqueio compartilhado é usado na tabela realizada até o final da instrução, evitando possíveis locks de tabela, o que pode gerar em alguns momentos situações como: Dados Fantasmas ou Leitura Suja.

 

Comportamento do Minimal Logging

Como qualquer outro tipo de recurso e funcionalidade o Minimal Logging também apresenta um comportamento bastante diferente dependendo do ambiente, a seguir a Figura 1 apresenta um comparativo entre cenários que podem permitir uso do Minimal Logging ou Full Logging:

  • Tabelas com Índices Clusterizados ou Não – Clusterizados;
  • Tabelas que possuem Heap;
  • Tabelas com Dados existentes; e
  • Tabelas consideradas vazias.

Minimall-1

 

Figura 1 – Comparativo entre cenários de uso do Minimal Logging.

Observação: Vale ressaltar que quando uma tabela apresenta dados e faz uso pelo menos um Índice Clusterizado, por questão de boas práticas e integridades dos dados, o SQL Server por padrão vai fazer uso e forçar a escrita de log.

 

Utilizando o Minimal Logging – Cenário 1 – Table Heap

Pois bem, vamos começar a utilizar o Minimal Logging, neste Cenário 1, estaremos utilizando a seguinte configuração:

  • Banco de Dados: MinimalLogging;
  • Recovery Model: Bulk-Logged; e
  • Tabelas: TableWithHeap e TableAuxWithHeap.

 

Para montar o ambiente para o Cenário 1, devemos o utilizar o Código 1 apresentado abaixo:

 

— Código 1 – Criando o Cenário 1 –

— Criando o Banco de Dados —

Create Database MinimalLogging

Go

 

— Acessando o Banco de Dados —

Use MinimalLogging

Go

 

— Alterando o Modelo de Recuperação do Banco de Dados —

Alter Database MinimalLogging

Set Recovery Bulk_Logged;

Go

 

— Verificando a existência das Tabelas —

If Object_ID(‘TableWithPrimaryKey’) IS NOT NULL

Drop Table TableWithPrimaryKey;

 

If Object_ID(‘TableWithHeap’) IS NOT NULL

Drop Table TableWithHeap;

 

If Object_ID(‘TableAuxWithHeap’) IS NOT NULL

Drop Table TableAuxWithHeap;

Go

 

— Criando as Tabelas —

Create Table TableWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

 

Create Table TableAuxWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

Go

 

— Inserindo a Massa de Dados —

Declare @Contador Int

Set @Contador =1

 

While @Contador <=15000

Begin

Insert Into TableAuxWithHeap(Coluna1) Values (@Contador)

 

Set @Contador +=  1

End

Para ilustrar ainda mais a geração de informações para serem registradas em Log, você pode observar que utilizamos anteriormente um bloco de código com o comando While, sendo executando com base na condição de quantidade de registros.

 

Após realizarmos a inserção de registros na tabela auxiliar TableAuxWithHeap, o próximo passo é realizar a inserção desta mesma quantidade de registros na tabela TableWithHeap.

 

–Inserindo dados na Tabela TableWithHeap utilizando o Table Hint (TABLOCK) para forçar a geração do Minimal Logged—

Insert Into TableWithHeap With(TABLOCK)

Select * From TableAuxWithHeap

 

Pronto, todos inseridos e distribuídos entre nossas tables, o próximo passo é verificar o registro de informações em nosso Transact-Log através da Function Fn_DBLog, conforme o bloco de código abaixo:

— Validando o Registro de Atividades no Log —

Select Top 10 operation As ‘Operation’,

Context  As ‘Contexto’,

[log record fixed length] As ‘Tamanho Fixo do Registro’,

[log record length] As ‘Tamanho do Registro de Log’,

AllocUnitId As ‘Unidade de Alocação’,

AllocUnitName As ‘Nome da Unidade de Alocação’

From fn_dblog(null, null)

Where allocunitname=’dbo.TableWithHeap’

Order By [Log Record Length] DESC;

Observe a coluna Tamanho do Registro de Log, ela apresenta o tamanho do nosso Log, conforme apresenta a Figura 2:

Minimall-2

 

Figura 2 – Coluna – Tamanho do Registro de Log.

Outra maneira de confirmarmos neste momento o tamanho do nosso Log, é consultar o local em que se encontra o nosso Banco de Dados MinimalLogging e observar o tamanho do arquivo de Log, neste caso, o MinimalLogging_Log.LDF, que apresenta 94,528 Kb, conforme ilustra a Figura 3:

Minimall-3

Figura 3 – Tamanho do Arquivo de Log em disco.

 

Espero que até aqui você tenha conseguido acompanhar e entender um pouco mais sobre o Minimall Logging, nossa próxima caminhada é com base Cenário 1, criado anteriormente, ilustrar como o Fully Logged poder ser ocasionado, para isso vamos utilizar o Código 2, apresentado abaixo:

–Inserindo dados na Tabela TableWithHeap utilizando o Table Hint (NOLOCK) para forçar a geração do Fully Logged—

 

Insert Into TableWithHeap

Select * From TableAuxWithHeap With(NOLOCK);

 

— Validando o Registro de Atividades no Log —

Select Top 10 operation As ‘Operation’,

Context  As ‘Contexto’,

[log record fixed length] As ‘Tamanho Fixo do Registro’,

[log record length] As ‘Tamanho do Registro de Log’,

AllocUnitId As ‘Unidade de Alocação’,

AllocUnitName As ‘Nome da Unidade de Alocação’

From fn_dblog(null, null)

Where allocunitname=’dbo.TableWithHeap’

Order By [Log Record Length] DESC;

Observe a coluna Tamanho do Registro de Log, ela apresenta o tamanho do nosso Log, conforme apresenta a Figura 4:

Minimall-4

 

 Figura 4 – Coluna – Tamanho do Registro de Log.

Outra maneira de confirmarmos neste momento o tamanho do nosso Log, é consultar o local em que se encontra o nosso Banco de Dados MinimalLogging e observar o tamanho do arquivo de Log, neste caso, o MinimalLogging_Log.LDF, que apresenta 138,496 KB, conforme ilustra a Figura 5:

Minimall-5

 

 Figura 5 – Tamanho do Arquivo de Log em disco.

Se você se lembra quando realizamos a primeira carga de dados inserindo 15.000 registros a coluna: Tamanho do Registro de Log e também o Tamanho Físico do Arquivo de Log, apresentavam valores bem menores. Sendo assim, podemos considerar a Table Hint NoLock utilizada em conjunto com o Select para inserção das outras 15.000 linhas de registro, aumentou de forma considerável o Tamanho do nosso Registro de Log e também do Arquivo.

A Figura 6 apresenta este comparativo e um gráfico para ilustrar estas diferenças:

Minimall-6

 

Figura 6 – Diferenças entre Minimally Logged e Fully Logged.

 

Basicamente tivemos um aumento de 68% no tamanho de nosso arquivo de MinimalLogging_Log.LDF, como também, nossos registros com tamanho fixo cresceram de forma espantosa ocupando um tamanho de 8104.

 

Utilizando o Minimal Logging – Cenário 2 – Table With Primary Key And Index Clustered

Pois bem, vamos começar a utilizar o Minimal Logging, neste Cenário 2, estaremos utilizando a seguinte configuração:

  • Banco de Dados: MinimalLogging;
  • Recovery Model: Bulk-Logged; e
  • Tabelas: TableAuxWithHeap e TableWithPrimaryKey.

 

Para montar o ambiente para o Cenário 1, devemos o utilizar o Código 1 apresentado abaixo:

 

— Código 1 – Criando o Cenário 2 –

— Criando o Banco de Dados —

Create Database MinimalLogging

Go

 

— Acessando o Banco de Dados —

Use MinimalLogging

Go

 

— Alterando o Modelo de Recuperação do Banco de Dados —

Alter Database MinimalLogging

Set Recovery Bulk_Logged;

Go

 

— Verificando a existência das Tabelas —

If Object_ID(‘TableAuxWithHeap’) IS NOT NULL

Drop Table TableAuxWithHeap;

Go

 

— Criando as Tabelas —

Create Table TableAuxWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

 

Create Table TableWithPrimaryKey

(Coluna1 INT Primary Key, Coluna2 Char(6000), Coluna3 Char(2000));

Go

 

–Inserindo dados na Tabela TableWithPrimaryKey utilizando o Table Hint (NOLOCK) para forçar a geração do Fully Logged—

Insert Into TableWithPrimaryKey

Select * From TableAuxWithHeap With(NOLOCK);

Go

Muito bem, realizamos mais este inserção de 15.000 linhas de registros, só que agora trabalhando na Tabela TableAuxWithHeap que possui em sua estrutura um campo como chave primária e automaticamente um índice Clusterizado.

Observe o valor apresentado na coluna Tamanho do Registro de Log, conforme apresenta a Figura 7. Nesta nova operação só que agora na Tabela TableWithPrimaryKey, nos apresenta um valor bem acima dos dados apresentados nos comparativos anteriores.

Vale ressaltar que esta tabela também estava vazia, mas a mesma apresenta uma chave primária e índice clusterizado, por isso, o SQL Server vai trabalhar com a escrita de Log.

Minimall-7

 

 Figura 7 – Coluna – Tamanho do Registro de Log.

Como destaquei anteriormente toda transação realizada em uma tabela que possua Chave Primária, será armazenada em Transact – Log e desta forma, estaremos trabalhando com Fully Logged, fazendo então que o nosso arquivo de log sofra um crescimento, conforme apresenta a Figura 8 a seguir:

Minimall-8

 

Figura 8 – Tamanho do Arquivo de Log em disco.

 

Fica fácil observar e entender que quando estamos trabalhando com Minimal Logging o SQL Server vai tentar de todas formas gerar e armazenar o menos possível de informações em nosso arquivo de log, mas em alguns casos isso não é possível por questões do funcionamento padrão do SQL Server e garantia do dado que esta sendo armazenado.

Mesmo trabalhando com tabelas vazias o Minimal Logging criou um volume bem pequeno de informações para escrita no arquivo de Log.

Vou encerrar este post, deixando o último comparativo que realizei analisando o processo de inserção de registros em uma Heap Table em comparação a inserção em uma Tabela com Índice Clusterizado e Chave Primária, conforme apresenta a Figura apresentada abaixo:

Minimall-9

 

 Figura 9 – Diferenças entre Minimally Logged e Fully Logged em Empty Table.

 

Conclusão

Podemos dizer que o Minimal Logging é uma prática que possui o objetivo de gerar o menos possível de leitura e escrita nos arquivos de log, ocupando menos espaço em disco e menor tempo para backup e restauração de dados, em contra partida não nos permite realizar a restauração de dados com base em pontos específicos.

 

Espero que você tenha gostado de post.

Mais uma vez obrigado por sua atenção e visita.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

3 comentários em “Simulando – Minimal Logging – Microsoft SQL Server 2008 R2 e 2012.”

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s