Atraso no truncamento do Transaction Log: Snapshot Replication

Galera,

Algumas vezes podemos nos deparar com cenários aonde o Transaction Log de uma database configurada com Recovery Model FULL não para de crescer e o mesmo nunca é truncado, mesmo que os backups de transaction log ocorram com frequência. Este comportamento pode ocorrer por várias razões: transações ativas, backups/restores em andamento, replicação, entre outros motivos, os quais você pode conferir neste link.

Neste post vamos falar de um motivo específico no atraso do truncamento do log de transação: REPLICATION. Mais especificamente SNAPSHOT REPLICATION e de como aplicar uma possível solução de contorno para situações de emergência.

Para conferirmos o motivo da não reutilização do log de transação podemos sempre utilizar a coluna log_reuse_wait_desc da tabela de sistema sys.databases, conforme consulta abaixo:

01

Como podemos conferir na imagem abaixo o motivo do nosso log não ser truncado/reutilizado é devido a uma replicação configurada na instância do SQL Server.

02

Se esta replicação configurada for do tipo SNAPSHOT, seu log de transação estiver cheio, você estiver sem espaço em disco e não souber o que fazer, comece executando o comando abaixo na sua database:

03

Captura de Tela 2016-02-24 às 00.28.29

Se o resultado do comando DBCC OPENTRAN for semelhante ao retorno exibido acima há uma saída.

Na sua base de dados que está com o log de transação cheio e não pode ser reutilizado devido a replicação, conforme conferido anteriormente na coluna log_reuse_wait_desc execute o comando abaixo:

05

Após a execução deste comando as pendências de replicação devem estar resolvidas e o seu log de transação poderá ser reutilizado. Agora você deve garantir que a replicação está replicando todas alterações de estrutura para garantir a integridade da mesma.

Atenção: O comando sp_repldone deve ser utilizado apenas em situações de emergência. Esta não deve ser considerada como a solução definitiva do problema e sim apenas uma solução de contorno.

 

Referências:

https://msdn.microsoft.com/pt-br/library/ms173775(v=sql.120).aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

Atualizando uma coluna IDENTITY

Galera,

Recebi uma dúvida hoje: “É possível atualizar uma coluna IDENTITY de um registro específico?”
Respondendo: a atualização direta com a utilização do comando UPDATE não é permitida. Mas, se você realmente precisa realizar este procedimento por algum motivo específico, há uma solução de contorno bem simples.

Primeiro vamos criar o cenário para realizarmos o procedimento.

  • Criação da tabela:

Captura de Tela 2016-01-14 às 20.41.20

  • Adição de registros:

Captura de Tela 2016-01-14 às 20.41.25

  • Consulta:

Captura de Tela 2016-01-14 às 20.42.26

Agora vamos fazer uma tentativa de atualização da coluna id criada como IDENTITY na tabela TesteIdentity:

Captura de Tela 2016-01-14 às 20.45.47
Como podemos ver, ocorreu o erro 8102 “Cannot update identity column ‘id'”, pois não é possível atualizarmos diretamente um campo com a propriedade IDENTITY.
Para resolvermos este problema vamos precisar apenas de dois passos:

  1. Deletar o registro que queremos atualizar (Não se esqueça de fazer backup da sua tabela);
  2. Inserir o registro novamente com o seu novo valor para a coluna ID utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON.

Agora que já sabemos a solução, vamos tentar atualizar o mesmo registro do cenário criado anteriormente utilizando os passos acima.

  • Vamos excluir o registro com o valor de id = 1:

Captura de Tela 2016-01-14 às 20.46.24

  • E por último vamos inserir o registro com o seu novo valor para a coluna id utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON:

Captura de Tela 2016-01-14 às 20.47.23

O resultado é a “atualização” do id = 1 para id = 20, conforme abaixo:

Captura de Tela 2016-01-15 às 01.43.38

  • Se esquecermos de utilizar a cláusula SET IDENTITY_INSERT [nomeTabela] ON, o SQL Server irá retornar o erro 544 “Cannot insert explicit value for identity column in table ‘TesteIdentity’ when IDENTITY_INSERT is set to OFF“:

Captura de Tela 2016-01-15 às 01.35.58

IMPORTANTE:

  • Nunca se esqueça de retornar a cláusula IDENTITY_INSERT para OFF, pois o SQL Server permite que apenas uma tabela por vez utilize esta opção. Caso esqueça de desligar esta propriedade para uma tabela e tente ativar para uma segunda tabela o SQL Server irá retornar o erro abaixo:Captura de Tela 2016-01-15 às 01.43.07
  • Se o valor inserido for maior que a IDENTITY atual da tabela, automaticamente o SQL Server irá alterar o IDENTITY para o valor inserido, como podemos ver na imagem abaixo ao inserir um novo valor após termos inserido o valor com a cláusula SET IDENTITY_INSERT ON:

Captura de Tela 2016-01-15 às 01.49.38

Captura de Tela 2016-01-15 às 01.50.01

  • Se for necessário visualizar ou alterar o valor atual da IDENTITY podemos utilizar o comando DBCC CHECKIDENT. No nosso caso vamos primeiramente conferir o valor atual da IDENTITY, alterá-lo para 30 e por último inserir um novo registro na tabela.

Conferindo o valor atual da IDENTITY. Notem que o valor atual é 21.

Captura de Tela 2016-01-15 às 02.01.38

Alterando o valor da IDENTITY para 30:

Captura de Tela 2016-01-15 às 02.02.06

Conferindo novamente o valor atual da IDENTITY. Notem que agora o valor atual foi alterado para 30, conforme comando executado anteriormente. O valor 21 que ainda permanece é referente ao valor máximo da coluna IDENTITY existente na tabela:

Captura de Tela 2016-01-15 às 02.02.20

Inserindo um novo registro na tabela TesteIdentity:

Captura de Tela 2016-01-15 às 02.02.43

Como podemos ver após alterarmos o valor da IDENTITY para 30, o próximo valor inserido foi o 31, conforme evidência abaixo:

Captura de Tela 2016-01-15 às 02.03.28

Por hoje é isso galera! Se tiverem dúvidas, críticas ou sugestões de assuntos podem enviar pelos comentários ou até mesmo por e-mail: felipe@lauffer.me

Obrigado!