Obtendo o Tamanho em Megabytes dos Arquivos de Dados e Arquivos de Log de Transações no Microsoft SQL Server 2005, 2008 e R2

Pessoal, bom dia.

Tudo bem?

Vocês podem estar estranhando a minha ausência nos últimos dias na atualização do meu blog, peço desculpas, mas é por um bom motivo, uma grande conquista profissional que há muito tempo que estava esperando.

Pois bem, com base nas atividades que estou realizando atualmente em meu no trabalho, vou postar a dica de hoje.

Acredito que em algum momento, todos nós já tivemos a necessidade de se preocupar com a capacidade de armazenamento de dados existente em nossos Servidores, ainda mais quando trabalhamos em grandes Corporações, em que o volume de dados possui uma rotatividade muito grande e sua tendência natural é sempre aumentar.

Justamente esta preocupação esta sendo a minha primeira atividade profissional realizada nesta nova caminhada. No ambiente em que estou trabalhando possuímos aproximadamente 12 Servidores Microsoft SQL Server entre as versões 2000, 2005, 2008 e R2, todos rodando a edição Enterprise em plataformas 32 e 64 bits.

Vocês podem e devem estar imaginando o volume de informações, bancos de dados, tables, stored procedures entre outros conceitos relacionados ao SQL Server que este ambiente possui, sua complexidade administrativa, como também a necessidade de se manter tudo na mais completa harmonia.

É ai que começa a minha jornada, como realizar o Levantamento do tamanho de cada banco de dados em diferentes servidores? Para esta necessidade, decidi utilizar um dos recursos mais criticados do mundo, o temido Cursor.

Para aqueles que não conhecem ou não sabem o que é um Cursor, vou dar um breve explanação.

O que é um Cursor?

Cursor é um recurso existente no SQL Server com base em estrutura de lógicas condicionais que permitem a rolagem de registros dentro de uma estrutura de armazenamento temporária de valores.

Através do Cursor temos a possibilidade de deslocar os ponteiros lógicos existentes nesta estrutura com a finalidade de movimentar-se entre os registros, como por exemplo:

  • Deslocando-se do primeiro registro até o último registro;
  • Deslocando-se do último registro para o primeiro registro;
  • Deslocando-se para um registro anterior; e
  • Deslocando-se para um registro posterior.

Esta movimentação força o SQL Server a percorrer algumas linhas de registro até chegar ou encontrar o ponto desejado, o que normalmente gera um possível sobrecarga de processamento, ao bastante criticado por a grande maioria dos profissionais de Banco de Dados e Administração de Servidores de Banco de Dados.

Mas também o Cursor também apresenta vantagens que devem ser destacadas:

  • Facilidade em se criar estrutura lógicas para armazenamento de volume de dados;
  • Facilidade em se percorrer grande quantidade de registros dentro desta estrutura;
  • Armazenamento de Dados em tabelas físicas ou temporárias;
  • Armazenamento de Dados obtidos durante a leitura do Cursor em Variáveis;
  • Utilização de Funções ou Stored Procedure dentro do seu processo de busca de registros;
  • Trabalha com sintaxe em padrões ISO e Transact-SQL;
  • Pode ser utilizado desde versões 6.5 até 2008 R2 com a mesma sintaxe e estrutura; e
  • Ferramenta para geração de massa de dados em arquivos e relatórios.

Bom, agora que já conhecemos um pouco sobre Cursor, vou começar a destacar como consegui resolver a minha necessidade.

Trata-se de um código bem simples, que basicamente utiliza uma Temporary Table chamada #RelacaoBancos, utilizada para armazenar os valores de Tamanho do Arquivo de Dados e Tamanho do Arquivo de Log, armazenados nas variáveis @SizeDataFile, @SizeLogFile, ambas do tipo Inteiro.

 Os valores armazenados nestas variáveis é obtido através da seguinte transação:

  1. Set @SizeDataFile=(Select Sum(((size*8)/1024)) from SysFiles Where GroupId >= 1) 
  2. Set @SizeLogFile=(Select Sum(((size*8)/1024)) from SysFiles Where GroupID = 0)       

Onde estou utilizando a System Table SysFiles, consultando a Coluna Size, realizando uma multiplicação da Coluna Size * 8, e em sequência dividindo este resultado por 1024 para obter os valores em Megabytes.

Observação: A sysfiles é utilizada para retornar informações sobre cada arquivo de dados e log que compõem um determinado banco de dados.

Estes valores são capturados para cada Banco de Dados, durante a execução do Cursor denominado Cursor_EspacoBancodeDados, armazenando na variável @DatabaseName o nome de cada banco de dados que esta sendo acessado no momento da volta do Cursor.

O Cursor Cursor_EspacoBancodeDados esta vinculado lógicamente a seguinte transação:

  1. DECLARE Cursor_EspacoBancodeDados CURSOR FOR
  2. SELECT Name FROM MASTER..SYSDATABASES
  3. WHERE  Name NOT IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)
  4. And    status <> 66048
  5. Order By Name

Observem que este Select esta retornando somente o Nome dos Bancos de Dados de Usuário que possuem Status diferente de 66048, informações contidas na System Table: sysDatabases. Este Status indica para o SQL Server a necessidade de retornar somente os Bancos de Dados que apresentam atualmente o Status de On-Line!!!!

Observação: A sysDatabases, é responsável em apresentar para o usuário uma linha para cada banco de dados em uma instância de Microsoft SQL Server. Quando SQL Server é instalado pela primeira vez, sysdatabases contém entradas para os bancos de dados master, model, msdb e tempdb.

A seguir apresento o Código 1, responsável em obter e armazenar o Espaço Físico ocupado por cada Arquivo de Dados(Data File) e Arquivo de Log de Transações(Log File).

O motor de execução deste código encontra-se armazenado dentro da variável @Comando do Tipo Varchar(100) e posteriormente executada através da instrução:

  • Exec(@Comando)

Neste caso esta fazendo uso da chamada Query Dinamicy ou Query Dinâmica, através do conceito de criação em tempo de Execução de Projeto, para Transações e Querys no SQL Server.

— Código 1: Relação de Bancos de Dados, valores de Sum(Data File) e Sum(Log File) —

 

— Acessando o Banco de Dados TEMPDB —
Use TempDB

Go

— Verificando a existência da Table #RelacaoBanco —

If Object_Id(‘TEMPDB..#RelacaoBancos’) IS NOT NULL

 Begin

  Truncate Table #RelacaoBancos

 End

 Else

 Begin

  Create Table #RelacaoBancos

  (Codigo TinyInt Identity(1,1),

   DatabaseName Varchar(100),

   Space_DataFile Int,

   Space_LogFile Int)

 End

 

— Declarando Variáveis Auxiliares —

DECLARE @DatabaseName varchar(30), @Comando varchar(1000)

 

— Declarando o Cursor_EspacoBancodeDados, vinculando ao Select Name from Master..sysDatabases —

DECLARE Cursor_EspacoBancodeDados CURSOR FOR

SELECT name FROM MASTER..SYSDATABASES

WHERE  name NOT IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)

and    status <> 66048

Order By Name

— Abrindo o Cursor —

OPEN Cursor_EspacoBancodeDados

— Avançando o Cursor para o próximo registro e armazenando a coluna Name na variável @DatabaseName —

FETCH NEXT FROM Cursor_EspacoBancodeDados

INTO @DatabaseName

 

— Bloco de Execução Condicional —

WHILE @@FETCH_STATUS = 0

BEGIN

   SELECT @Comando =  ‘SETNOCOUNTON’ + char(10) +

          ‘USE ‘ + @DatabaseName + ” + char(10) +

 

          ‘

           DECLARE @SizeDataFile Int,

                   @SizeLogFile Int

           Set @SizeDataFile=(Select Sum(((size*8)/1024)) from SysFiles Where GroupId >= 1)

           Set @SizeLogFile=(Select Sum(((size*8)/1024)) from SysFiles Where GroupID = 0)       

           Insert Into TempDB..#RelacaoBancos(DatabaseName, Space_DataFile, Space_LogFile)

                              Values(‘+””+@DatabaseName+””+’,’+’@SizeDataFile’+’,’+’@SizeLogFile’+’)’

 

 Exec(@Comando)

 

 FETCH NEXT FROM Cursor_EspacoBancodeDados

 INTO @DatabaseName

END

 

— Fechando o Cursor —

CLOSE Cursor_EspacoBancodeDados

— Removendo o Cursor lógicamente da Memória —

DEALLOCATE Cursor_EspacoBancodeDados

— Consultando as Informações capturadas durante a Execução do Cursor —

Select Upper(DatabaseName) ‘Database’,

       Space_DataFile As ‘Size Data File in MBs’,

       Space_LogFile As ‘Size Log File in MBs’

from #RelacaoBancos

A Figura 1 apresenta um resultado similar ao obtido após a execução do Código 1.

Figura 1 – Relação de Bancos de Dados obtidos após a execução do Código 1.

 

Por questões de Segurança, não posso apresentar o nome dos Bancos de Dados, o mais importante é o valor apresentado nas Colunas:

  • Space_DataFile As ‘Size Data File in MBs’
  • Space_LogFile As ‘Size Log File in MBs’

Ambas as colunas apresentam os valores consumidos pelos Arquivos de Dados e Arquivos de Log na unidade de medida em Megabytes.

Bom galera, vou encerrar este artigo aqui, espero que todos tenham gostado e entendido o objetivo deste material.

Mais uma vez agradeço a sua atenção e visita, nos encontramos em breve.

Até mais.

Anúncios

Sobre Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulações e Reconhecimentos: Microsoft MVP, MCC, MSTC e MIE.
Esse post foi publicado em Dicas, Mundo SQL Server, Scripts, SQL Server, VIRTUAL PASS BR e marcado , , , . Guardar link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s