Monitorando crescimento de tabelas usando powershell


English Version :
 
articlepresentation_ST
 
Pessoal,
 
neste script eu monitoro o crescimento de todas as tabelas de todos os servidores (previamente cadastrados no servers.txt).
Não me preocupei com unused space, somente data e index pois eu quero saber o crescimento real delas.
 
Primeiramente temos que criar o txt..no meu caso fica em "C:\Servers\servers.txt"….e o txt tem o nome dos servidores que serão monitorados, algo como :
 
server1
server2
server3\instance1
 
depois criamos a tabela no repositorio (servidor e database)..no meu caso é server1 database dba e uma trigger para calcular o crescimento diario
 
create table tablegrowth ( DDate datetime default getdate(),
    ServerName varchar(50),
    DatabaseName varchar(50),
    TableName varchar(50),
    SpaceIndexUsed float,
    SpaceDataUsed float,
    Total float,
    growing float
     )
 
 
 
create  trigger tr_insert on tablegrowth instead of insert
as
begin
declare  @DDate datetime,
   @ServerName varchar(50),
   @DatabaseName varchar(50),
   @TableName varchar(50),
   @SpaceIndexUsed float,
   @SpaceDataUsed float,
   @Total float,
   @growing float,
   @growinglast float,
   @totallast float,
   @FirstTime bit
  
–retrive all data without @growing since last collect
  
select @DDate = ddate,
  @ServerName = Servername,
  @DatabaseName = DatabaseName ,
  @TableName = TableName ,
  @SpaceIndexUsed = SpaceIndexUsed,
  @SpaceDataUsed = SpaceDataUsed ,
  @Total = SpaceIndexUsed + SpaceDataUsed
from inserted
–retrive last total
select @totallast = @total
from tablegrowth
where ServerName = @ServerName
and  DatabaseName = @DatabaseName
and  TableName = @TableName
and  ddate = ( select MAX(ddate)
     from tablegrowth
     where ServerName = @ServerName
     and  DatabaseName = @DatabaseName
     and  TableName = @TableName
    )
   
–if  does not have any lines, i assume the value being insert – First collect for this table
set @firsttime = 0
if @totallast is null
set @firsttime = 1
 
set  @totallast = ISNULL(@totallast,@total)
–now..i calculate the growing since the last collection
if  @firsttime = 0
set @growing = @Total – @totallast
else
set @growing = @Total
— now insert the values
insert into tablegrowth(
       DDate ,
       ServerName ,
       DatabaseName,
       TableName ,
       SpaceIndexUsed ,
       SpaceDataUsed ,
       Total ,
       growing
      ) 
values     (
       @DDate ,
       @ServerName ,
       @DatabaseName,
       @TableName ,
       @SpaceIndexUsed ,
       @SpaceDataUsed ,
       @Total ,
       @growing
      )
end
 
Esta trigger pega o ultimo valor de Dados + index , soma e tira do total em KB da ultima coleta gerando o crescimento desta coleta.
 
Agora o script PS
 
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
# Database and server repository
$ServidorCentral = "SERVER1"
$DatabaseCentral = "DBA"
#Today date
$Datahoje = get-date -format "yyyy-MM-dd hh:mm:ss"
foreach ($svr in get-content "C:\servers\servers.txt" )
{
$Servidor=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $data = $Servidor.Databases| where-object {$_.IsSystemObject -eq $FALSE -and
    $_.IsAccessible -eq $TRUE -and $_.name -ne "DBA" } | foreach {
     $DatabaseName = $_.name
$ServerName = $Servidor.Name
        foreach  ($tabelas in $Servidor.Databases[$_.name].tables ) {
   if (!$tabelas.IsSystemObject)
   {
    $tablename = $tabelas.name
    $SpaceIndexUsed = $tabelas.IndexSpaceUsed
    $SpaceDataUsed = $tabelas.DataSpaceUsed
   
  
    $sql = "insert into TableGrowth (DDate,ServerName,DatabaseName,TableName,SpaceIndexUsed,SpaceDataUsed) values
    (‘$DataHoje’,’$ServerName’,’$DatabaseName’,’$TableName’,$SpaceIndexUsed,$SpaceDataUsed)"
    Write-Host $SQl
    Invoke-Sqlcmd -ServerInstance $ServidorCentral -Database $DatabaseCentral -Query $sql
   }
  }
}
}
Este script usa o SQLPS.exe que é minishell do sql server. Se quisermos usar o powershell normal temos que adicionar os snapins do sql server nele.
 
Bom, agora é so schedular toda noite num job e pronto..depois é so tirar os valores, por exemplo
select   ServerName,
  DatabaseName,
  TableName,sum(growing ) Growing
from  tablegrowth
where   ddate between ‘2009-01-01 00:00:00’ and ‘2009-01-01 23:59:00’
group by ServerName,DatabaseName,TableName
eu tenho o crescimento das tabelas por servidor e database em janeiro
 
Se colocarmos num report no reporting services e transformar num dashboard, todo dia de manhã temos esta posição…
 
É isso aí..
 
Abraços
 
 
 
 
 

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s