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!

Como utilizar o recurso Registered Servers no SQL Server Management Studio

Galera,
Sejam bem-vindos! Vamos ao primeiro post com uma dica básica, mas que pode ajudar muito no dia-a-dia e na organização de um DBA.

Dependendo da quantidade de servidores administrados, fica cada vez mais difícil de manter a organização e também de não esquecer daquele SQL Server de criticidade baixa que quase nunca é utilizado. Para resolver esse problema, vou mostrar um recurso bem simples do SQL Server que auxilia na organização dos servidores existentes.

O recurso é chamado de Registered Servers (Servidores Registrados). Para acessar o recurso no SQL Server Management Studio, você pode ir até o menu View > Registered Servers ou utilizar as teclas de atalho Ctrl + Alt + G:

Captura de Tela 2016-01-11 às 22.24.25

Isto irá habilitar a aba de servidores registrados no canto esquerdo da janela do Management Studio:

Captura de Tela 2016-01-11 às 22.41.12

Acessando esta nova aba de servidores registrados temos duas opções: Armazenar a lista de servidores de forma local (perfil do usuário) ou em um servidor específico de forma centralizada.

Captura de Tela 2016-01-11 às 22.26.02

Recomendo o armazenamento em um servidor centralizado. Desta forma, independente do cliente que você esteja utilizando, você pode se conectar a este servidor e ter a sua lista de servidores pronta e disponível. A única limitação que temos no caso do servidor centralizado é que todos os servidores que iremos registrar devem utilizar a forma de autenticação Windows Authentication. Se você possuir um servidor que só tenha acesso com autenticação SQL, deve optar pelo armazenamento local (Local Server Groups).

Para cadastrar um servidor de gerenciamento centralizado, devemos clicar com o botão direito de Central Management Servers e navegar até a opção Register Central Management Server.

Captura de Tela 2016-01-11 às 22.41.32

Na nova janela aberta devemos apenas informar o nome do servidor que irá centralizar a lista de servidores existentes no ambiente.

Captura de Tela 2016-01-11 às 22.26.55

Com o servidor central configurado podemos iniciar a organização da lista dos servidores de SQL Server criando grupos e registrando os servidores abaixo destes grupos.

Para criar um novo grupo podemos navegar até o servidor central, clicar com o botão direito em cima dele e escolher a opção New Server Group e por fim informar um nome e uma descrição para o novo grupo.

Captura de Tela 2016-01-11 às 22.41.43

Captura de Tela 2016-01-11 às 22.28.48

Captura de Tela 2016-01-11 às 22.29.07

Para registrar um servidor abaixo de um grupo podemos navegar até o grupo criado, clicar com o botão direito em cima dele, navegar até a opção New Server Registration, e por fim informar o nome do servidor e suas credenciais de acesso.

Captura de Tela 2016-01-11 às 22.42.10

Captura de Tela 2016-01-11 às 22.32.55

Com isso você pode ir cadastrando todos os seus servidores de SQL e organizá-los por tipo de ambiente, localização, versões, etc.

Captura de Tela 2016-01-11 às 22.35.27

É possível também exportar os servidores registrados para um arquivo para serem usados posteriormente ou importados em outro servidor. Para exportar um grupo de servidores clique com o botão direito em cima do grupo e navegue até o item Tasks > Export, em seguida escolha o local e o nome do arquivo de destino.

Captura de Tela 2016-01-11 às 23.45.33

Captura de Tela 2016-01-11 às 23.47.34

Captura de Tela 2016-01-11 às 23.48.53

Com isso o grupo de servidores foi exportado com sucesso. Tá mas e agora como importa em outro servidor?

Praticamente do mesmo jeito. Clicando com o botão direito em cima do servidor de gerenciamento, navegando até o item Tasks > Import e selecionando o arquivo criado na exportação.

Captura de Tela 2016-01-11 às 23.53.04

Captura de Tela 2016-01-11 às 23.52.38

Captura de Tela 2016-01-11 às 23.52.45

Um benefício bacana que o recurso Registered Servers proporciona é realizar uma consulta em um grupo de servidores de uma forma bem simples. Clicando com o botão direito em cima de um grupo de servidores ou até mesmo do servidor de gerenciamento central e navegando até o item New query podemos executar uma consulta ao mesmo tempo em todos os servidores de um grupo.

Captura de Tela 2016-01-11 às 23.02.02

Veja abaixo dois exemplos de uma consulta feita em cima do grupo PRD e outra em cima do servidor de gerenciamento central utilizando a função SERVERPROPERTY().

Captura de Tela 2016-01-11 às 22.54.38

Captura de Tela 2016-01-11 às 22.55.16

É isso pessoal. Espero que tenham gostado!