CAPTURANDO PROCESSAMENTO DO SQL SERVER MINUTO A MINUTO COM SYS.DM_OS_RING_BUFFERS

Neste post demonstrarei como capturar o processamento do SQL Server minuto a minuto utilizando a SYS.DM_OS_RING_BUFFERS.

Observação: esta view retorna os últimos 256 minutos.

Scripts: http://sdrv.ms/15jYBU9

Versão para SQL 2008, 2008 R2 e 2012

 

1

Versão para SQL 2005:

1

 

 

Até mais!!!

Anúncios

ALTERAR COLLATION DE BANCO DE DADOS SQL SERVER

Neste post demonstrarei como alterar o collation de um banco de dados SQL Server.

Vamos aos passos:

  1. Verificar o collation atual do banco de dados;
    1
  2. Rodar o script para alteração do collation do banco de dados;
    1
  3. Validar a alteração;
    1
  4. Agora temos que verificar as colunas que temos no formato texto para alterarmos o collation  uma a uma:
    1
  5. Query para geração dos scripts;
    1
  6. Copiar os comandos gerados, abrir um New Query e executar os scripts de alteração;
    1
  7. Consultando os collations após a alteração;
    1

 

Pronto, collations alterados!

Obs: após finalizar recrie todos seus índices.

Até mais!!!

CONSULTANDO E ALTERANDO OPÇÕES AVANÇADAS NO SQL SERVER

Neste post demonstrarei como visualizar e alterar as opções avançadas do SQL Server.

A partir da versão SQL Server 2008 existe uma view chamada [sys].[configurations] onde podemos visualizar as opções avançadas do SQL Server.

1

Se filtrarmos pela coluna is_advanced igual a 1 irá mostrar todas as opções que são avançadas no SQL Server.

SELECT  [configuration_id],[name],[value],[minimum],[maximum]
               ,[value_in_use],[description],[is_dynamic],[is_advanced]
FROM [master].[sys].[configurations]
where [is_advanced] = 1

Quando executamos a store procedure  sp_configure sem ativar as opções avançadas, as opções de configurações serão limitadas.

1

Para ativar as opções avançadas será necessário executar os passos abaixo:

  1. Ativação;
    1
  2. Reconfiguração.
    1

Após a ativação todas as opções estarão disponíveis para configuração.

1

Podemos observar que o valor que está em uso é igual a zero, o que indica que está desativado.

Segue as formas de visualizar:

1

Quando tentamos executar um recurso avançado desativado que no nosso caso será o uso da store procedure xp_cmdshell  ocorrerá erro.

A procedure xp_cmdshell  é responsavél executar comandos MS-DOS dentro do SQL Server.

Link: http://technet.microsoft.com/pt-br/library/ms175046.aspx

1

Agora realizarei a ativação das store procedure:

1

Após a ativação ou desativação sempre será necessário a execução do comando reconfigure.

1

Caso queira consultar a alteração basta realizar uma das consultas:

1

Executando a store procedure xp_cmdshell após a ativação:

1

Se tentar ativar a uma opção avançada com “show advanced options” desativado uma exceção será gerada.

Ex:

1

Para construção deste post utilizei o SQL Server 2012 Developer.

Até mais!!!

POLICY MANAGEMENT – IMPLEMENTANDO CONTROLE EM CRIAÇÃO DE STORE PROCEDURES

Neste post mostrarei como criar e ativar controle na criação de store procedure  utilizando o Policy Management.

Neste caso vamos colocar restrição para que não seja possível criar store procedure  que usem o nome com inicio “SP_”.

Vamos aos passos:

  1. Abra o SQL Management Studio;
  2. Ir até o diretório Policy Management conforme mostrado abaixo;
    1
  3. Clique com botão direito sobre Policies e selecione New Policy;
    1
  4. Informar o nome da política;
    1
  5. Selecionar New condition… ;
    1
  6. Informar o nome da condição, selecionar Facet igual a Store Procedure e incluir as restrições que estamos abordando neste post;
    1
  7. Caso queira incluir uma descrição sobre a condição e clica em Ok;
    1
  8. Após a criação selecionar Evaluation Mode como On change: Prevent;
    1
  9. Utilizaremos a categoria Default, informar a descrição, informar o texto que será apresentado caso a politica seja acionada e clicar em Ok;
    1
  10. Após a criação da política a mesma será apresentada conforme imagem abaixo. Também é válido salientar que a mesma esta desativada. olhando no simbolo da politica, podemos ver que tem uma seta vermelha. Isto indica que está desativa.
    1
  11. Próxima etapa é habilitar a política;
    1
  12. Após habilitar a política a seta vermelha desaparecerá conforme mostrado abaixo;
    1
  13. Clicar com botão direito sobre a política e selecionar Evaluate:
    1
  14. Clicar em Close;
    1
  15. Agora tentaremos criar uma store procedure fora do padrão. Abaixo segue a política sendo acionada;
    1
  16. Se tentarmos criar a store procedure conforme o padrão adotado, a criação ocorre normalmente;
    1

Até mais!!!

INTEGRATION SERVICE (SSIS) – EXPORTAÇÃO DE ARQUIVOS POR CONDIÇÃO DE DADOS

Neste post irei demonstrar como fazer como realizar exportação de “N” arquivos conforme as suas condições de dados.

Recursos utilizados:

  • SQL Server 2012
  • Visual Studio 2010

Para este post usarei a seguinte estrutura:

2

Inclusão  dos dados:

2

Vamos a criação do Integration Services (SSIS):

  1. Abrir o Visual Studio;
  2. Clicar em New Project;
  3. Criar o projeto conforme imagem abaixo;1
  4. Projeto após a criação;
    1
  5. Incluir o componente Data Flow Task;
    1
  6. Selecionar Data Flow;
    1
  7. Incluir Source Assistant e selecionar SQL Sevrer, New e clicar em Ok;
    1
  8. Informar Server Name (Instância), usuário, senha e selecionar o banco de dados;
    1
  9. Testar a conexão e clicar em Ok;
    1
  10. Após a criação a conexão aparecerá no Connection Managers;
    1
  11. Dar um duplo clique em OLE DB Source;
    1
  12. Informar Data access mode como SQL Command e preencher o SQL Command Text com a query que retornará os dados;
    1
  13. Selecionar Columns, marcar todas a colunas para exportação e clicar e Ok;
    1
  14. Próxima etapa é inserir o componente Conditional Split;
    1
  15. Ligar os componentes;
    1
  16. Duplo clique em Conditional Split;
    1
  17. Configurar as condições conforme imagem abaixo e clicar em Ok;
    1
  18. Próxima etapa é incluir 3 Flat File Destination;
    1
  19. Agora colocaremos o destino para cada condição;
  20. Ligar o componente Conditional Split com cada Flat File Destination;
    1
    1
    1
  21. Após ligar os componentes a estrutura fica da seguinte forma;
    1
  22. Agora vamos fazer a configuração de formatação do arquivo de saída e seu destino;
  23. Dar um duplo em cada Flat File Destination e configurar conforme os passos abaixo;
    1
  24. Clicar em New, selecionar Delimited e clicar em Ok;
    1
  25. Informar o nome da conexão, o destino do arquivo, formato que será exportado e clicar em OK;
    1
  26. Após a configuração;
    1
  27. Visualizar o mapeamento das colunas e clicar em Ok;
    1
  28. Repetir a etapa 27 para os demais arquivos;
  29. Após a configuração dos arquivos de saída a Packege deve estar desta forma;
    1
  30. Executando o pacote;
    1
  31. Arquivos gerados;
    1

Até mais!!!

REPLICAÇÃO SQL SERVER – SNAPSHOT

Iniciando a série de posts referente aos modos de replicação do SQL Server. Nestes posts utilizarei a o SQL Server 2012.

Para quem está estudando para prova é interessante uma passada neste post.

Componente SQL Server necessário:

1

Para este post vou ilustrar o seguinte cenário:

Sou administrador dos bancos de dados SQL Server de um centro de pesquisa latino americano, a maior parte de coleta das informações são realizadas no Brasil e a parte de análise das informações são realizadas no escritório Uruguaio. O Servidor principal fica localizado no Brasil. Os pesquisadores só analisam as competências de exames realizadas no mês anterior ou inferior. Para a análise os pesquisadores executam vários relatórios. As informações coletadas no Brasil geram varias transações que concorrem com as execuções dos relatórios dos pesquisadores Uruguaios. Somente os pesquisadores Uruguaios acessam os relatórios para análise. As equipes de pesquisa estão reclamando bastante dos tempos de resposta das operações realizadas no sistema.

O escritório Uruguaio também tem licença de SQL Server disponível e em operação.

Uma boa opção para o cenário ilustrado acima é o uso da replicação SQL Server no modo Snapshot.

Vamos a configuração:

  1. No SSMS, expandir Replication e clicar em New Publication.
    1
  2. Clicar em Next;
    1
  3. Selecionar Yes e clicar em Next;
    1
  4. Clicar em Next;
    1
  5. Selecionar o banco de dados e clicar em Next;
    1
  6. Selecionar Snapshot publication e clicar em Next;
    1
  7. Selecionar as tabelas que serão publicadas e clicar em Next;
    1
  8. Neste cenário não utilizaremos filtros. Clicar em Next;
    1
  9. Selecionar o itens conforme abaixo, clicar em Change  configurar o agendamento e clicar em Next;
    1
    2
  10. Neste caso usarei o usuário configurado no meu SQL Server Agent. Clicar em Ok;
    1
  11. Clicar em Next;
    1
  12. Selecionar create the publication  e clicar em Next;
    1
  13. Informar o nome da publicação e clicar em Finish;
    1
  14. Aguarde o processo de configuração e clique em Close;
    1
  15. Após a configuraçã a publicação estará apresentada da seguinte forma;
    1

Podemos observar que ainda não temos nada configurado na instância do escritório Uruguaio.

1

Próxima etapa será a configuração do Subscription.

  1. Selecionar Local Subscriptions e clicar em New Subscriptions;
    1
  2. Clicar em Next;
    1
  3. Clicar em Next;
    1
  4. Clicar em Next;
    1
  5. Selecionar
    1
  6. Conectar nas isntancia do escritorio Uruguaio;
    1
  7. Selecionar a instância Uruguai e clicar em New Database;
    1
  8. Informar o nome do banco de dados e clica em Ok;
    1
  9. Clicar em Next;
    1
  10. Clique no botâo mostrado a baixo;
    1
  11. Neste cenário utilizarei o usuário configurado no SQL Server Agent;
    1
  12. Clicar em Next;
    1
  13. Selecionar conforme mostrado abaixo e clicar em Next;
    1
  14. Selecionar conforma abaixo e clicar em Next;
    1
  15. Selecionar Create the subscription(s) e clicar em Next;
    1
  16. Antes de finalizar a configuração vamos ver como está a instancia do escritório Uruguaio;
    O banco de dados foi criado mas as tabelas ainda não foram replicadas.
    1
  17. Clicar em Finish;
    1
  18. Aguardar o processo de configuração e clicar em Next;
    1
  19. Jobs Criados;
    1
  20. Após a replicação podemos ver o banco de dados criado e com os dados replicados;
    1
  21. Vou mostar um pouco do Replication Monitor;
    1
  22. Replication Monitor;
    11
    11

Replicação configurada. Basta alterar o pontamentos dos relatórios para o banco de dados Snapshot e os problemas de desempenho fica solucionado para ambos escritórios.

Para mais infomações sobre replicação SQL Sever acessar o link abaixo.

http://technet.microsoft.com/pt-br/library/ms151832.aspx

Até mais!!!

REPARANDO BANCO DE DADOS COM TABELA CORROMPIDA

Neste post vou demonstrar como reparar um banco de dados com tabela corrompida.

1 – Primeiramente executaremos o comando DBCC CHECKDB (‘BizTalkDTADb’) WITH ALL_ERRORMSGS.

DBCC results for ‘BizTalkDTADb’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for ‘sys.sysrscols’.
There are 1573 rows in 19 pages for object “sys.sysrscols”.
DBCC results for ‘sys.sysrowsets’.
There are 261 rows in 4 pages for object “sys.sysrowsets”.

There are 3 rows in 1 pages for object “dta_ServiceState”.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:735693) is missing a reference from previous page (1:780581). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:735987) is missing a reference from previous page (1:797797). Possible chain linkage problem.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data), page (1:737741). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.
Msg 8928, Level 16, State 1, Line 1
Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data): Page (1:737741) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data), page (1:738831). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.
Msg 8928, Level 16, State 1, Line 1
Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data): Page (1:738831) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data), page (1:740654). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data): Page (1:740654) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:738831) was not seen in the scan although its parent (1:779016) and previous (1:847346) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:739486) is missing a reference from previous page (1:865528). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:745241) is missing a reference from previous page (1:830415). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057595638775808, alloc unit ID 72057595657977856 (type In-row data). Page (1:745626) was not seen in the scan although its parent (1:734204) and previous (1:745625) refer to it. Check any previous errors.
Msg 8976, Level 16, State 1, Line 1


CHECKDB found 0 allocation errors and 2370 consistency errors in table ‘Tracking_Parts1’ (object ID 2117582582).
DBCC results for ‘Tracking_Spool2’.
There are 0 rows in 0 pages for object “Tracking_Spool2”.
CHECKDB found 0 allocation errors and 2917 consistency errors in database ‘BizTalkDTADb’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (BizTalkDTADb).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2 – Após a execução vamos alterar o banco de dados SINGLE_USER executando o comando ALTER DATABASE BizTalkDTADb SET SINGLE_USER WITH ROLLBACK IMMEDIATE.

1

3 – Executaremos o comando DBCC CheckDB (‘BizTalkDTADb’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS para reparação do banco de dados.

1

4 – Executaremos o comando ALTER DATABASE BizTalkDTADb SET MULTI_USER para voltar a base de dados para multiusuário.

5 – Após a reparação vamos fazer uma checagem para ver se o banco de dados voltou a sua integridade.

Executar novamente o comando: DBCC CHECKDB (‘BizTalkDTADb’) WITH ALL_ERRORMSGS

1

Podemos observar que após os procediementos o banco de dados voltou a sua integridade.

Até mais!!!!