Monitorando o crescimento de tabelas usando Powershell

Primeiramente temos que criar o txt..no meu caso fica em “C:Serversservers.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 )

— Criando a Trigger que calcula o crescimento diário dos databases
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 @total 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

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:serversservers.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 (para usarmos em servidores SQL2K e SQL2K5 também)

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
laertejunior

Avaliação: