CDC – Tabelas ativas

Você precisa listar as tabelas com CDC habilitado?

Apenas utilize o script abaixo dentro da sua database com CDC habilitado.


select name
from sys.tables
where is_tracked_by_cdc = 1
order by name

Retorno:
9902

Espero que ajude!

CDC – Databases ativas

Você precisa listar as databases com CDC habilitado?

Apenas use o script abaixo.


SELECT
name,
is_cdc_enabled
FROM sys.databases
WHERE
database_id > 4 AND
is_cdc_enabled = 1
ORDER BY name

Retorno:

99

Espero que ajude!

CDC – View job configuration

Para visualizar a configuração dos jobs de captura (capture) e limpeza (cleanup) é simples, basta utilizar o comando abaixo:


EXECUTE sys.sp_cdc_help_jobs

O retorno trará informações sobre o tempo de retenção, quantidade de registros deletados a cada iteração, intervalo de polling, máximo de scans realizados no log de transação a cada ciclo de verificação, máximo de transações a serem processadas a cada ciclo de verificação.

09

CDC – Change Data Capture

O Change Data Capture ou mais conhecido como CDC é um recurso disponível no SQL Server que armazena todas as alterações (INSERT, UPDATE, DELETE) realizadas em uma determinada tabela.

Por padrão as databases vem com o CDC desabilitado. Caso seja necessário o CDC deve ser ativado para cada database. Assim que o CDC é ativado é criado um schema chamado cdc e também são criadas alguns objetos e estruturas de controle dentro deste schema, como por exemplo:

  • cdc.change_tables
  • cdc.ddl_history
  • cdc.index_columns
  • cdc.lsn_time_mapping

Mesmo que você ative o CDC para uma database específica por padrão as tabelas vem com o CDC desativado, portanto é preciso ativá-lo para cada tabela necessária. Assim que o CDC é ativado para uma tabela específica é criada uma tabela com a mesma estrutura e 5 colunas adicionais no schema cdc:

  • cdc.nomeSchema_nomeTabela

Também são criadas duas funções para extração dos dados:

  • cdc.fn_cdc_get_all_changes_nomeSchema_nomeTabela
  • cdc.fn_cdc_get_net_changes_nomeSchema_nomeTabela

A extração das alterações realizadas em uma determinada tabela é responsabilidade da instância de captura (capture instance). A origem de extração das alterações utilizada pela instância de captura é o arquivo do log de transação (transaction log) da database. A instância de captura é executada através de um job criado no SQL Server Agent:

  • cdc.nomeDatabase_capture

Também é criado um outro job no SQL Server Agent, que é o job de limpeza (cleanup). Este job de limpeza é responsável pela limpeza das tabelas do CDC mantendo por padrão 4320 minutos (3 dias) de alterações dentro de cada tabela do CDC e removendo 5000 linhas a cada iteração.

  • cdc_nomeDatabase_cleanup

A seguir podemos visualizar a arquitetura do Change Data Capture:

01

Referências:

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

Resource Database

Conhece essa database?

Já viu ela no Management Studio?

Viu alguma vez um DatabaseID 32767?

A resource é uma Database somente leitura com DBID 32767, que contém todos os objetos de sistema do SQL Server, todos aqueles objetos que utilizamos do schema SYS, como por exemplo o objeto sys.indexes. Todos esses objetos são persistidos nessa base de dados, mas são logicamente visíveis em todas as databases através do schema SYS.

Uma curiosidade é que o máximo de databases que uma instância de SQL Server suporta é 32767 e este número é reservado para a resource database.

Não adianta tentar utilizar a função nativa DB_NAME() para retornar o nome da database pelo ID 32767, pois o retorno será NULL.

Referências:

https://blog.sqlauthority.com/2011/05/10/sql-server-resource-database-id-32767/

SQL Server Start Time

Para conferir o horário de início do serviço da sua instância de SQL Server basta utilizar um dos scripts abaixo.

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT login_time
FROM sys.dm_exec_sessions
WHERE session_id = 1;

SELECT create_date
FROM sys.databases
WHERE name = 'tempdb';

Gostou? Dúvidas? Críticas?
Deixe seu comentário!

TRACE FLAGS

As trace flags são utilizadas para mudar uma característica da instância de SQL Server ou um comportamento em particular.

Elas também são utilizadas em diagnósticos de problemas de desempenho, debug de stored procedures ou até mesmo podem ser recomendadas pelo suporte da Microsoft para corrigir um comportamento específico que esteja impactando negativamente o seu ambiente.

É uma boa prática sempre realizar o teste da trace flag em um ambiente não-produtivo, assim como é uma boa prática realizar o teste em ambiente não-produtivo de qualquer alteração de configuração na sua instância de SQL Server.

Existem 3 escopos para uma trace flag:

1. query: específicas para o contexto de uma consulta.
2. session: ativas para uma conexão específica e são visíveis apenas dentro desta sessão.
3. global: são ativas para toda a instância de SQL Server e visíveis para todas as conexões.

As trace flags de escopo de sessão (session) são ligadas e desligadas com os comandos abaixo:
Como ligar uma trace flag de sessão:

DBCC TRACEON (3604)

Como desligar uma trace flag de sessão:

DBCC TRACEOFF (3604)

Para ligarmos e desligarmos uma trace flag de escopo global utilizamos os mesmos comandos, mas com um parâmetro adicional, conforme abaixo.

Como ligar uma trace flag global:

DBCC TRACEON (1222, -1)

Como desligar uma trace flag global:

DBCC TRACEOFF (1222, -1)

Precisa listar todas as trace flags ativas no seu ambiente e sessão?
Listar TRACE FLAGS ativas

Você utiliza alguma trace flag no seu ambiente?
Comente as trace flags que você utiliza!

Referências:

Trace flags: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

DBCC TRACEON: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-transact-sql

DBCC TRACEOFF: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceoff-transact-sql