Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis


 

Olá comunidade, boa tarde.

Tudo bem? Estamos se aproximando dos últimos dias de férias ou recesso para grande maioria dos professores e profissionais de educação espalhados por todo Brasil. E ai, já esta preparado para voltar a luta? Posso dizer tranquilamente que sim, eu estou pronto para voltar a conviver com meus alunos e amigos de trabalho.

Antes de começarmos a falar sobre o post de hoje, nada mais justo começar agradecendo como de costume a você está aqui neste momento acessando meu blog. Espero que possa ter encontrado o que precisa, bem como, esteja gostando do conteúdo publicado, fique a vontade para entrar em contato expressando suas opiniões e demais pensamentos.

Neste novo post da sessão Dica do Mês, vou apresentar um recurso que imagino ser conhecido por muitos ou principalmente pelos profissionais de banco de dados, estou me referindo as Trace Flag ou sinalizador de rastreamento em português.

Você já ouvir falar sobre isso ou já utilizou? Eu imagino que sim pois aqui no meu blog diversos posts e artigos foram publicado ao longo dos últimos anos sobre este tipo de recurso. Hoje mais especificamente vou destacar o uso da Trace Flag 9292, por acaso você já utilizou em algum momento esta trace flag?

Bom independente da sua reposta vamos conhecer um pouco mais sobre ela, sua forma de uso e como poderá nos ajudar a entender ainda mais o funcionamento das estatísticas e seus chamados objetos úteis para análise do plano de execução.

E ai esta curioso para saber um pouco sobre este recurso? Eu estou, sendo assim, vamos em frente, seja bem vindo ao post Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis.

Vamos em frente…..


Introdução

Todos sabemos que as estatísticas desempenham um papel muito importante dentro do SQL Server, como também, sabemos que as estatísticas são usadas pelo otimizador de consultas para gerar o plano de execução para cada consulta.
Então a questão é, podemos verificar qual objeto estatístico é útil para a execução da consulta? Sim, usando o Trace Flag do SQL Server 9292. Este é um do sinalizador de rastreamento que pode ser usado durante a solução de problemas.
Esse sinalizador de rastreamento é usado para obter o relatório sobre objetos de estatísticas considerados como “interessantes” ou “úteis” pelo otimizador de consulta durante a compilação ou recompilação de consulta.

Adicionada ao Microsoft SQL Server 2008 após a instalação do service pack 1 e mantida até as atuais versões, no momento em tomamos a decisão de utilizar a Trace Flag 9292, orientamos o SQL Server a apresentar todos os objetos estatísticos considerados úteis por parte do plano de execução para realizar o processamento e retorno dos dados.

O uso da Trace Flag 9292 dentro de uma sessão ou query específica, nos ajuda a entender e conhecer como as estatísticas e seus elementos podem mudar totalmente a maneira que o plano de execução é idealizado, armazenado e processado.

Através dela podemos obter um relatório sobre as estatíticas para cada objeto envolvido em nossa query, onde estes supostos objetos devem ser considerados úteis, ou melhor dizendo válidos e aplicáveis no decorrer do caminho realizado até a apresentação do resultado.

Esta é uma trace flag que pode ser usada durante a resolução de problemas, onde sua função é apresentar na guia de mensagens do Management Studio, um pequeno cabeçalho contendo informações estatísticas sobre cada componente útil e válido para formas os dados estatísticos de processamento da query. Este cabeçalho é conhecido como Stats header loaded.

Para ativar a trace flag utilize o comando DBCC TraceON (9292) ou DBCC TraceOFF (9292) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

Agora que já conhecemos um pouco mais sobre os conceitos desta trace flag, chegou a hora de colocar as mãos no teclado e conhecer melhor o funcionamento da flag 9292 e de que forma ela poderá impactar o trabalho do Microsoft SQL Server, nos permitindo identificar os objetos úteis processados ou candidatos a processamento por parte do Query Processor em conjunto com Execution Plan.

Preparando o ambiente

Nosso ambiente será bastante simples, basicamente criaremos um banco de dados denominado DBTrace9292, constituído de uma tabela denominada TBTrace9292, para tal utilizaremos o Bloco de Código 1 que apresenta a criação dos respectivos objetos:

— Bloco de Código 1 —
— Criando o Banco de Dados DBTrace9292 —
Create Database DBTrace9292
Go

— Acessando —
Use DBTrace9292
Go

— Criando a Tabela TBTrace9292 —
Create Table TBTrace9292
(Codigo Int Identity(1,1) Primary Key,
Valores Int,
Descricao Varchar(100))
Go

Após a criação dos objetos básicos, nosso próximo passo será a criação de índice nonclustered para coluna Valores que nos permitirá fazer o uso de estatísticas de processamento para esta coluna durante o processo de inserção de dados, conforme apresenta o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —
— Criando o Índice NonClustered IND_TBTrace9292Valores —
Create NonClustered Index IND_TBTrace9292Valores on TBTrace9292(Valores)
Go

— Inserindo uma linha de registro na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(2000,’pedrogalvaojunior.wordpress.com’)
Go

— Inserindo 1.000 linhas de registros na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(4000,’pedrogalvaojunior.wordpress.com’)
Go 1000

Note que existir uma pequena mais importante diferença entre os dois Inserts, no primeiro estamos inserindo uma linha de registro na tabela TBTrace9292. No segundo criamos em tempo de execução um pequeno bloco de inserção de linhas, sendo este processado 1.000 vezes, inserindo então 1.000 linhas.

Seguindo em frente, o Bloco de Código 3 será utilizado para criarmos uma Stored Procedure denominada P_PesquisarValores como forma para buscar os respectivos registros armazenados na tabela TBTrace9292. A seguir apresento o Bloco de Código 3:

— Bloco de Código 3 –
— Criando a Stored Procedure P_PesquisarValores —
Create Procedure P_PesquisarValores @Valor int
As
Begin
Select Descricao from TBTrace9292
Where Valores = @Valor
OPTION (RECOMPILE)
End
Go

Se você for como eu, normalmente gosto de fazer uma análise de código antes de colocar em prática no meu ambiente, sendo assim, vamos lá. Analisando de forma superficial a Stored Procedure P_PesquisarValores a princípio não apresenta nada muito especial ou de grande complexidade em seu código, mas sim o uso de opção Recompile que justamente vai orientar o plano de execução a recompilar a  P_PesquisarValores no momento da sua execução, forçando assim que a cada execução um novo plano de execução seja criado em conjunto com uma nova análise estatística e seus demais elementos.

O próximo passo consiste na pesquisa de um dos valores armazenados na tabela TBTrace9292 através da execução e processamento da Stored Procedure P_PesquisarValores. Para este passo vamos utilizar o Bloco de Código 4 a seguir, antes de sua execução recomendo habilitar a apresentação do Plano de Execução Atual no SQL Server Management Studio através do botão Include Actual Execution Plan ou simplesmente através da tecla de atalho CTRL+M.

— Bloco de Código 4 —
— Habilitando as TraceFlags 9292 e 3604 —
DBCC TraceOn(9292,3604,-1)
Go

Dica: Utilize o comando DBCC TraceStatus WITH NO_INFOMSGS para verificar quais Trace Flags estão habilitadas em qual nível de escopo.

— Execuntando a Stored Procedure P_PesquisarValores —
Exec P_PesquisarValores 4000
Go

Pois bem, após a execução do Bloco de Código 4, o Microsoft SQL Server realizou o processamento da nossa Stored Procedure P_PesquisarValores realizando uma busca de todas as linhas de registros que possuem o valor 4.000, onde obrigatoriamente foram retornadas 1.000 linhas de registros.

Até ai nada de novo ou surpreende, o que justamente eu quero mostrar para vocês é o que o Management Studio apresenta na guia Messages após o processamento do Bloco de Código 4, conforme apresenta a Figura 1 abaixo:

Note que o cabeçalho retornado pela Trace Flag 9292 conhecido como Stats header loaded esta apresentando os objetos realmente utilizados para o processamento de nossa query, bem como, os objetos considerados úteis e necessários para criação, compilação e processamento do plano de execução envolvidos na execução, sendo eles:

  • Database: DBTrace9292;
  • Table: TBTrace9292,
  • Index: IND_TBTrace9292Valores, sendo este do tipo Nonclustered;
  • Column: Valores; e
  • EmptyTable: False, representa que a tabela possui linhas de registro.

Perfeito, perfeito, ai esta a prova que a Trace Flag 9292 nos permite identificar de forma simples, coerente e muito intuitiva todos os objetos envolvidos na execução de uma query, stored procedure ou demais elementos que permitem a criação de um plano de execução.

Desta forma, chegamos ao final de mais um post, tendo a sensação de dever cumprido, espero que você tenha gostado, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.


Referências

https://thomaslarock.com/2016/06/sql-server-Trace-flags/

https://pedrogalvaojunior.wordpress.com/2016/02/26/02-para-que-serve/

DBCC TRACEOFF (Transact-SQL)

DBCC TRACEON (Transact-SQL)

DBCC TRACESTATUS (Transact-SQL)

EXECUTE (Transact-SQL)

Query Hints (Transact-SQL)

Post Anteriores

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Muitas vezes temos dificuldade em entender como um determinado recurso ou funcionalidade esta sendo utilizada pelo Microsoft SQL Server, com também, de que maneira este elemento poderá impactar nosso ambiente.

Neste post foi possível apresentar como a Trace Flag 9292 nos permite identificar quais objetos estão sendo utilizando durante o processamento e execução de uma determinada query. Um recurso de fácil configuração tanto para ser ativado como também desativado a qualquer momento ou necessidade.

Recomendo que você realize diversos testes e validações antes de fazer qualquer tipo de uso de uma trace flag em seu ambiente de produção, isso também se aplica a Trace Flag 9292.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos em breve com mais um post da sessão Dica do Mês.

Valeu….

Pesquisando o mesmo registro em diversas tabelas no SQL Server 2008, R2 e 2012.

Dica – Pesquisando o mesmo registro em diversas tabelas no SQL Server 2008, R2 e 2012.


Pessoal, bom dia.

Gostaria de lançar uma pergunta para vocês.

Quem nunca teve a necessidade de pesquisar a mesa informação em mais de uma tabela no Banco de Dados, mais especificamente no Microsoft SQL Server?

Pois bem, é justamente a resposta para este questionamento que é um dos mais comuns nos fóruns, listas de usuários e até mesmo dentro da sua empresa, que esta dica pretende responder.

Como todos sabem, um simples Banco de Dados é organizado logicamente por uma estrutura de armazenamento de dados chamada Tabela. Este estrutura é dividida de forma lógica e física em pequenas áreas de armazenamento chamadas colunas.

Seguindo este conceito, podemos imaginar a dificuldade para qualquer Administrador de Banco de Dados, Analista de Sistemas, Desenvolvedor ou qualquer outro profissional da área de TI, BI ou Tomada de Decisão tem a dificuldade para se encontrar o mesmo dado espalhado por estas estruturas de armazenamento.

Com base nesta necessidade, e também respondendo a diversos questionamentos que venho sempre recebendo em meus e-mails, posts que respondo diariamente nos Fóruns MSDN. Como não sempre, sabemos por onde começar pesquisei um pouco de informações na Internet e acredito ter consegui desenvolver uma possível solução ou ideia de solução para este cenário.

A possível solução:

Conforme destaquei anteriormente, desenvolvi uma possível solução para este cenário, denominada “Mecanismo de Pesquisa de Dados em Objetos”. O código utilizado por esta funcionalidade esta dividido em sete partes para facilitar o entendimento e criação deste mecanismo de pesquisa.

Parte 1 – Considerada o ponto inicial da criação do Mecanismo, bastante simples e de fácil compreensão. Trata-se da Criação das Tabelas que serão utilizadas como repositório para os dados.

— Parte 1: Verificando a existência das Tabelas —

If Exists(Select Object_Id(‘Tabela1’))

 Begin

  Drop Table Tabela1

 

  CREATE TABLE Tabela1

   (Coluna1 INT,

    Coluna2 INT,

    Coluna3 INT)

 End

 Else

  Begin

   CREATE TABLE Tabela1

    (Coluna1 INT,

     Coluna2 INT,

     Coluna3 INT)

  End 

 

If Exists(Select Object_Id(‘Tabela2’))

 Begin

  Drop Table Tabela2

 

  CREATE TABLE Tabela2

   (Coluna1 INT,

    Coluna2 INT,

    Coluna3 INT)

 End

 Else

  Begin

   CREATE TABLE Tabela2

    (Coluna1 INT,

     Coluna2 INT,

     Coluna3 INT)

  End  

 

If Exists(Select Object_Id(‘Tabela3’))

 Begin

  Drop Table Tabela3

 

  CREATE TABLE Tabela3

   (Coluna1 INT,

    Coluna2 INT,

    Coluna3 INT)

 End

  Else

  Begin

   CREATE TABLE Tabela3

    (Coluna1 INT,

     Coluna2 INT,

     Coluna3 INT)

  End

Parte 2 – Responsável pela população de dados, para tabelas criadas anteriormente:

— Parte 2: Inserindo os registros —

INSERT INTO Tabela1 VALUES (1, 2, 3)

INSERT INTO Tabela1 VALUES (0, 7, 9)

INSERT INTO Tabela1 VALUES (3, 4, 2)

 

INSERT INTO Tabela2 VALUES (2, 2, 2)

INSERT INTO Tabela2 VALUES (3, 9, 5)

INSERT INTO Tabela2 VALUES (1, 6, 8)

 

INSERT INTO Tabela3 VALUES (4, 0, 7)

INSERT INTO Tabela3 VALUES (6, 5, 1)

INSERT INTO Tabela3 VALUES (4, 7, 9)

Parte 3 – Relaciona-se a declaração de variáveis, utilizadas como parâmetros de entrada de valores como forma de inteligência para o Mecanismo de Pesquisa de dados. Mas não é somente isso que esta parte do código faz, existe um ponto importante relacionado à variável @ValordePesquisa, esta variável é o elemento definido na estrutura do código responsável em receber o valor que será pesquisado.

— Parte 3: Declarando as variáveis —

DECLARE @Comando VARCHAR(1000),

                @ComandoTransact VARCHAR(100),

                @ValordePesquisa INT,

                @TABLE_NAME VARCHAR(20),

                @Coluna_NAME VARCHAR(20)

 

Set @Comando = ”

Set @TABLE_NAME = ”

Set @Coluna_NAME = ”

 

Set @ComandoTransact = ‘SELECT ”?”, ”^”, COUNT(*) AS TOTAL FROM ? WHERE ^ = @ UNION ALL’ + CHAR(10)

 

Set @ValordePesquisa = 3 — Informe o valor a ser pesquisado dentro do Mecanismo.

Parte 4 –  Começamos a montar a estrutura responsável em pesquisar dos dados, com base, nas tabelas e colunas existentes em nosso ambiente. Fazemos a declaração de uma variável do tipo Cursor. Sendo este cursor, declarado com a opção Fast_forward, habilitando os otimizadores de desempenho existentes no SQL Server, visando otimizar a pesquisa de dados e também o armazenamento dos mesmos nas variáveis vinculadas ao Cursor.

— Parte 4: Declarando o CursordePesquisa para retornar o nome da tabela e nome da Coluna —

DECLARE CursordePesquisa CURSOR FAST_FORWARD

FOR SELECT TABLE_NAME,

COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

 

— Abrindo o CursordePesquisa —

OPEN CursordePesquisa FETCH NEXT FROM CursordePesquisa

INTO @TABLE_NAME, @Coluna_NAME

Parte 5 – Iniciamos o bloco condicional While, elemento importante no nosso ambiente, responsável em montar a estrutura de execução do Select armazenado na variável @Comando, fazendo um busca do nome das tabelas e suas respectivas colunas através das variáveis @TableName e @ColunaName, vinculadas ao CursordePesquisa, contendo os valores encontrados na execução de cada registro encontrado pelo Cursor.

— Parte 5: Iniciando o bloco condicional While —

WHILE @@FETCH_STATUS = 0

 BEGIN

  SET @Comando = @Comando + REPLACE(REPLACE(REPLACE(@ComandoTransact,’?’,@TABLE_NAME),’^’,@Coluna_NAME),’@’,@ValordePesquisa)

  FETCH NEXT FROM CursordePesquisa

  INTO @TABLE_NAME, @Coluna_NAME

 END

Parte 6 – Pode ser considerada uma das mais importantes para o funcionamento do Mecanismo de pesquisa. Ela tem como finalidade realizar a concatenação e União dos Selects conforme os mesmos vão sendo montados durante a execução do CursordePesquisa e do Bloco Condicional While.

— Parte 6: Realizando a Concatenação e União dos Selects —

SET @Comando = LEFT(@Comando,LEN(@Comando)-LEN(‘UNIONALL’)-2)

Parte 7 – Consiste na parte final do código, responsável em criar a Tabela Temporária #Resultados, utilizada para armazenar o resultado dos dados encontrados pelo CursordePesquisa. Nas parte do código, realizamos a inserção dos dados nesta tabela temporário através do comando Insert tendo como parâmetro para entrada de valores o comando Exec (@comando), fazendo a execução da variável @Comando, contendo em sua estrutura as Partes 4, 5 e 6 do Mecanismo de Pesquisa dos Dados.

— Parte 7: Criando a Tabela Temporária para armazenar os Resultados —

CREATE TABLE #Resultados

 (NomeTabela Varchar(20),

  NomeColuna VARCHAR(20),

  TotaldeRegistros INT)

 

— Inserindo os dados na tabela Resultados com base na execução do @Comando —

INSERT INTO #Resultados

Exec (@Comando)

 

— Encerrando o Cursor —

CLOSE CursordePesquisa

DEALLOCATE CursordePesquisa

 

— Apresentando os dados —

SELECT * FROM #Resultados

 

— Excluíndo a Tabela Temporária Resultados —

DROP TABLE #Resultados

A final da execução do nosso Mecanismo de Pesquisa de Dados em Objetos, temos o resultado apresentado na Figura 1:

Figura 1 – Resultado da pesquisa de dados, com base no valor informado na variável @ValordePesquisa.

Bom galera, é isso, chegamos ao final de mais dica, acredito que através deste código, muitos questionamentos, dúvidas e até mesmo dificuldades foram respondidas.

Espero que todos tenham gostado.

Nos encontramos em breve.

Até mais.

 

Conhecendo a Stored Procedure não documentada MSunmarkreplinfo

Dica – Conhecendo a Stored Procedure não documentada MSunmarkreplinfo


Galera, bom dia.

Tudo bem?

A dica de hoje tem o objetivo de apresentar uma das diversas Stored Procedures existentes no SQL Server, mas que não possuem documentação oficial por parte da Microsoft, estou se referindo a MSunmarkreplinfo, que recentemente acabei conhecendo e utilizando.

Para apresentar mais sobre esta Stored Procedure, vou contar um pouco do que aconteceu e como consegui encontrar esta relíquia.

Nos últimos dias um dos meus clientes solicitou a configuração de uma estrutura de Replicação Transacional para suas bases de dados, até ai sem problemas, todo processo de configuração do Publisher e Subscriber realizado normalmente, especificando os artigos que seriam replicados entre os servidores, horário de replicação, filtros, entre outros detalhes.

Pois bem, durante alguns dias o processo de replicação funcionou sem problemas, mas no último final de semana, um dos programadores da empresa tinha a necessidade de aplicar algumas atualizações ao ambiente, dentre os elementos que seriam atualizados uma das bases de dados que estava envolvida na replicação seria envolvida nesta atualização.

Dentre os procedimentos padrões definidos pelo desenvolvedor para aplicar as atualizações consistia em realização do backup e posteriormente fazer um detach do banco de dados, foi justamente neste ponto que os problemas começaram a aparecer!!!

Não sei exatamente por qual motivo mas somente realizando o detach do banco de dados as atualizações na aplicação e banco eram aplicadas corretamente e o ambiente voltava a funcionar. Em conversa com o programar informei que poderíamos manter a estrutura de replicação e no processo de detach realizar alguns procedimentos específicos para os bancos envolvidos no processo de replica. Mas como santo de casa não faz milagre, o cara ficou louco, e disse que desta forma não poderia mais trabalhar, então acabou sobrando pra mim, você já podem imaginar o que aconteceu. Tive que remover todo processo de replicação para este banco de dados, ai que vem o pior, durante o processo de remoção da estrutura de replicação o servidor sem qualquer motivo específico travou.

Com certeza vocês podem imaginar alguns dos principais problemas durante um processo de reconfiguração de um ambiente o servidor travar, mas para minha felicidade ou infelicidade o banco de dados, não sofreu maiores problemas, mas o SQL Server não conseguiu remover o status das tabelas envolvidas na replicação, como sendo artigos replicados, desta forma, todo e qualquer processo de alteração na estrutura ou até mesmo exclusão das tabelas não poderiam ser realizados.

Nesta momento, eu consegui entender que o SQL Server ainda estava trabalhando com a replicação mesmo que toda estrutura tivesse sido removida, o que naquele momento poderia dizer que exista então um processo de replicação fantasma, conhecida como Ghost Replication, algo muito comum de se acontecer quando realizamos alterações nas configurações de uma replicação transacional e ocorrem falhas no ambiente.

Mesmo assim, sabendo da possível existência da Ghost Replication, o programador, tentou excluir a tabela ou até mesmo realizar algumas alterações em sua estrutura, foi neste momento que ao realizar por exemplo um simples Drop Table ou Alter Table, o SQL Server apresentava a seguinte mensagem:

Unable to drop a table even after removing replication. Getting the following errors, when try to drop or alter the table:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table ‘t1’ because it is published for replication.
Server: Msg 4931, Level 16, State 1, Line 1
Cannot add columns to table ‘t1’ because it is being published for merge replication.

Básicamente a mensagem informava que era impossível realizar a exclusão ou alterações na tabela pois a mesma estava envolvida em um processo de replicação.

Ao entrar em contato comigo e alertar sobre esta situação, o programador destacou a impossibilidade de manter o ambiente em funcionamento sem poder aplicar as novas atualizações.

Foi justamente com base nesta situação e sabendo que o status das tabelas envolvidas na replicação não haviam sido alterados, comecei a realizar algumas tentativas de alteração direta nas tabelas e visões de catálogo de sistema existentes no SQL Server 2005, trabalhando diretamente com a Sys.Objetcs e Sys.SysObjects.

Dentre as diversas tentativas, tentei alterar as colunas ReplInfo e Is_Published existentes nas respectivas tabelas e visões de catálogo, mas para minha raiva não consegui realizar a alteração. O SQL Server retornava uma mensagem informando que existia dependência entre estes objetos de sistemas o que impossibilitava esta alteração.

Lógicamente neste instante o que me restava era tentar de alguma forma buscar mais informações, através do Books On-Line, como também, utilizando a própria Internet. Em ambos as alternativas praticamente encontrei os mesmos exemplos, procedimentos, dicas e sugestões. Mas como a esperança é a última que morre comecei a buscar mais informações nos Fóruns MSDN e TechNet nos Estados Unidos e foi justamente neste locais que encontrei informações e exemplos de como utilizar MSunmarkreplinfo.

Não encontrei muita coisa sobre esta Stored Procedure, mas o pouco que encontrei foi o suficiente para conseguir resolver o meu problema, e justamente esta solução que vou compartilhar com vocês, conforme o exemplo apresentado a seguir:

 — Exemplo: Utilizando a MSunmarkreplinfo para remover o Status de tabela envolvida em replicação —

SET NOCOUNT ON
DECLARE @tablename NVARCHAR(128), @RC INT

DECLARE curTable CURSOR FOR
SELECT [name] AS tbl FROM sys.tables

OPEN curTable
FETCH NEXT FROM curTable INTO @tablename

 WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename
FETCH NEXT FROM curTable INTO @tablename
END

 CLOSE curTable
DEALLOCATE curTable
GO

Com o código apresentado anteriormente consegui alterar de uma forma forçada as colunas ReplInfo e Is_Published na tabela de catálogo de sistema Sys.Objects e utilizada também pela Sys.SysObjects, desta forma, o programador já poderia realizar todas as alterações desejadas, mecher na estrutura das tabelas, como também, executar qualquer outro tipo de procedimento em seu ambiente.

Sendo assim, tudo voltou ao normal, mas o processo de replicação não pode mais ser configurado a este ambiente, fazendo-se necessário adotar outros processos de disponibilidade dos dados entre os servidores da empresa.

Espero que esta dica possa ter ilustrado um pouco de como é possível utilizar Stored Procedures não documentadas em algumas situações, vale ressaltar que qualquer procedimento desconhecido deve ser analisado e realizado em ambientes de teste.

Agradeço a sua visita, nos encontramos em breve.

Até mais.

Utilizando Backup de Filegroup no SQL Server 2008 – Parte IV

Dica – Utilizando Backup de Filegroup no SQL Server 2008 – Parte IV


Bom dia comunidade,

Estou de volta com mais uma parte da minha série de artigos relacionados a Backup de Filegroup. Neste nossa parte, vou começar a demonstrar como podemos aplicar os backups de filegroup em nosso ambiente. Agradeço a sua visita, tenha uma boa leitura.

Aplicando o Backup de Filegroup

A partir de agora vamos começar a demonstrar como podemos trabalhar com Backup de Filegroups, com base em nosso Banco de Dados SQL. O primeiro passo será a realização de um Backup Database, conforme apresenta a Listagem 7, e ilustrado
através da Figura 8. Este backup full será a base para que nosso ambiente possa ser recuperado em caso de falha e consiste no recurso principal necessário para os processos de restauração de dados, através de um restore de  database, log ou filegroup.

Figura 8. Backup Database SQLMagazine.

Com o backup full realizado, estamos seguros e protegidos contra qualquer eventualidade de falha ou erro em nosso ambiente. Sendo assim, o próximo passo será a realização do tão esperado Backup de Filegroup, onde iremos executar este procedimento para ambos os filegroups, conforme apresenta a Listagem 8.  

Listagem 8. Backup Filegroup – Primary e Secondary

— Bloco 1 —

Backup Database SQL

File = ‘SQL_Dados’,

Filegroup = ‘Primary’

To Disk = ‘C:\SQL\Backup-Primary-SQL.bak’

With Init, NoFormat, Description =’Backup Filegroup Primary’

Go

— Bloco 2 —

Backup Database SQL

File = ‘SQL_Secondary_Dados’,

Filegroup = ‘Secondary’

To Disk = ‘C:\SQL\Backup-Secondary-SQL.bak’

With Init, NoFormat,

Description =’Backup Filegroup Secundário’

Go

Como podemos observar utilizamos o comando Backup Database para realizar o backup do nosso banco de dados e seus respectivos filegroups, para ajudar na entendimento do comando Backup, vou descrever brevemente as opções utilizadas neste
procedimento:
Database: Especifica um backup completo do banco de dados;

File: Especifica o nome do arquivo de dados utilizado no processo de backup;

Filegroup: Especifica o nome do filegroup utilizado no processo de backup;

To Disk: Especifica o caminho fisico em disco rígido, pra o qual o arquivo de backup será armazenado;

Init: Especifica que todos os conjuntos de backup devem ser substituídos, mas preserva o cabeçalho de mídia. Se INIT estiver especificado, qualquer conjunto de backup existente naquele dispositivo será substituído, se as condições permitirem;

NoFormat: Especifica que a operação de backup preserva o cabeçalho da mídia e os conjuntos de backup existentes nos
volumes de mídia usados para esta operação de backup. Esse é o comportamento padrão;

Description: Especifica uma breve descrição para o arquivo de backup, com no máximo 255 caracteres.

O processo de Backup Filegroup é muito simples e prático, ainda mais dependendo do tamanho banco de dados realizado em questão de segundos, como foi o nosso caso. Agora vamos simular uma manipulação desastrosa de objetos, onde nossa principal tabela Produtos, será excluída do nosso ambiente, e através do restauração do Filegroup Primary, iremos recuperar este objeto e todos os seus respectivos dados, conforme apresenta a Listagem 9.

Listagem 9. Recuperando a tabela Produtos através da restauração de Filegroup

— Bloco 1 —

Use Master

Go

Backup Log SQL

To Disk = ‘C:\SQL\Backup-Log-SQL.bak’

With Init, Stats=10,

Description=’Backup Log Database SQL’

Go

Como estamos utilizando o Modelo de Recuperação Completo (Recovery Model Full), torna-se necessário realizar um Backup Log. Este backup tem como finalidade informar ao SQL Server o ponto de liberação para acesso e gravação de dados contidos nos filegroups que compõem nosso banco de dados. Caso este backup não seja restaurado nossos filegroups serão definidos como somente leitura.

— Bloco 2 –

Drop Table Produtos

Agora nossa tabela Produtos foi excluída de nosso ambiente, conforme apresenta a Figura 9. 

Figura 9. Tabela Produtos excluída.

Mas poderemos realizar sua recuperação através do comando Restore, recuperando inicialmente o filegroup Primary e posteriormente liberando os demais filegroups para gravação através do comando Restore Log.

 — Bloco 3 —

Use Master

Go

Restore Database SQL Filegroup = ‘Primary’ From Disk = ‘C:\SQL\Backup-Primary-SQL.bak’

With Partial,  NoRecovery,  Replace

Go

Como iremos realizar a restauração do filegroup Primary e nosso banco de dados possui dois filegroups, será
necessário utilizar a opção Partial, para informar ao SQL Server que a restauração será realizada de forma partial, com base no primeiro filegroup.

A opção NoRecovery, será utilizada para impedir a liberação do banco de dados após seu processo de Restauração. Já a opção Replace, tem como finalidade substituir o conteúdo existem atualmente no banco de dados, pelo conteúdo que esta sendo
restaurado através do Restore.

— Bloco 4 —

Use Mmaster

Go

Restore Log SQL From Disk = ‘C:\SQL\Backup-Log-SQLMagazine.bak’

With Recovery, Replace

Go

Neste outro Restore realizado, informamos o SQL Server para liberar o banco de dados para uso através da opção Recovery, realizando a substituição do conteúdo através da opção Replace.

Pronto nosso filegroup Primary, foi restaurado e nossa tabela Produtos também esta novamente disponível em nosso banco de dados, conforme apresenta a Figura 10.

Figura 10. Tabela Produtos restaurada após restauração do filegroup Primary.

Para verificar se tudo esta certo, podemos realizar uma consulta aos dados armazenados em nossa tabela Produtos, conforme
apresenta a Figura 11.

Figura 11. Consulta aos dados armazenados na tabela Produtos.

Bom mas nem tudo pode ser considerado fácil, aparentemente nosso ambiente esta integro e funcional, mas não é bem assim. Se tentarmos inserir ou consultar dados em uma das tabelas armazenada no filegroup Secondary, recebemos uma mensagem de erro informando que este filegroup encontra-se em offline, conforme apresenta a Figura 12.

Galera, vou encerrar mais esta parte, estamos finalizando este arquivo, nos encontramos nas próximas séries.

Mais uma vez agradeço a sua visita.

Até mais.

Processando uma Stored Procedure e armazenando o retorno em uma tabela

Dica – Processando uma Stored Procedure e armazenando o retorno em uma tabela.


Boa tarde, pessoal.

A dica de hoje tem o objetivo de demonstrar como podemos executar uma Stored Procedure no SQL Server 2005 e 2008, armazenando o seu retorno em uma tabela.

Em diversas situações utilizar uma Stored Procedure representa uma facilidade e agilidade para o Administrador de Banco de Dados em suas atividades. O que nos faz pensar que sempre a Stored Procedure poderá não atender e resolver os nossos problemas. Trata-se de um pensamento correto!!!

Mas se necessitarmos obter os valores de retorno após a execução da Stored Procedure? Isso é possível? Temos como armazenar estes valores em uma tabela? Para todas estes perguntas temos uma única resposta “SIM”. E com base nestes questionamentos e também em alguns posts que encontrei nos Fóruns MSDN Brasil e TechNet Brasil, vou postar esta pequena dica e espero que seja útil.

Eu desenvolvi um pequeno código, denominado Código 1, que ilustra a possibilidade de armazenar o retorno da execução da Stored Procedure de sistema: SP_SpaceUsed, que possui a função de retornar ao usuário informações sobre alocação de espaço ocupado por Tabelas, Databases e Índices.

A seguir apresento o Código 1:

— Código 1 —

— Criando uma nova tabela para armazenar todas as tabelas existentes em um Banco de Dados —

Create Table MinhasTabelas
(Codigo Int Primary Key Identity(1,1),
Nome Varchar(100))

— Inserindo a relação de Nomes de Tabelas após a execução da Sys.Tables —

Insert Into MinhasTabelas
SELECT Name FROM SYS.TABLES
WHERE TYPE=’U’

— Criando uma nova tabela para armazenar todas as informações retornadas pela SP_SpaceUsed —

Create Table EspacoOcupado
(Name VarChar(100),
Rows Int,
Reserved Varchar(10),
Data Varchar(10),
Index_Size Varchar(10),
Unused Varchar(10))

— Declarando uma Estrutura de Repetição – While —

Declare @Contador SmallInt,
@NomeTabela Varchar(100)

Set @Contador=1

While @Contador <= (Select COUNT(Codigo) from MinhasTabelas)
Begin

Select @NomeTabela=Nome from MinhasTabelas
Where Codigo=@Contador

— Inserindo o Retorno da Execução da SP_SpaceUsed na Tabela EspacoOcupado —

Insert Into EspacoOcupado
Exec SP_Spaceused @NomeTabela

Set @Contador +=1
End

— Consultando os dados armazenados na tabela EspacoOcupado —

Select * from EspacoOcupado

 

Bom pessoal, esta aqui mais uma simples dica, sem muita frescura, sempre direta no assunto, espero que todos gostem, e que mais uma vez possa ajudar nas suas atividades.

Agradeço a sua visita, nos encontros nas próximas dicas.

Até mais.